COUNTIF AND COUNTIFS Formulas

Another request for COUNTIF here. I’m using Airtable for members of a committee to rate suggestions. Each row looks something like:

[suggestion] [rating] [rating] [rating]

Where each committee member gets a column in which to put ratings. (This lets me give each member their own view showing only their ratings, so they aren’t influenced by one another’s thought.) Each rating is a number: 5, 2, 0, -5. (5 means “MUST DO THIS”, -5 means “WORST IDEA EVER”.)

In addition to summing the ratings to give each suggestion an aggregate score, I’d like to be able to count ratings to see how many people rated each one 5 or -5. This will tell me how polarizing the item is. An item that has an aggregate rating of 0 because everyone was indifferent to it is quite different from one that has an aggregate rating of 0 because half the committee thought it was great and the other half thought it was awful.

I was actually first looking for a MODE function, but there isn’t one, so I thought I’d kludge it with COUNTIF. But there isn’t COUNTIF either! This may be one of those dreaded situations where I copy my Airtable base into Excel just to perform a few basic functions. Please add both MODE and COUNTIF.

1 Like

Not sure that I understand the formula but this worked exactly for what I need! A COUNTIF would be easier but this is a good work around, Thank you!

My workaround right now is to create a field for each status I need to count then roll them up in a related table.

Example: Employee Status field is Active, Inactive, Starting, or Leaving. I create 4 hidden formula fields with If(Status=“Active”,1), If(Status=“Inactive”,1), etc then I can do a Rollup field on those fields to Count(values) or Sum().

1 Like

Need this for counting, number of outstanding actions that match a particular criteria.

I agree with Jack_Richards, this did exactly what I needed it to do but I don’t understand the formula AT ALL! Thank you though!

How could I use this faux count if to count a specific response like Completed and then convert that into a percentage of all response?

@Jason_Friedlander
Suppose you have a table of Tasks that can have a {Status} = “Complete”.

Supposed you have a table of Projects that can have many linked Tasks.

In your Tasks table, create a formula field; let’s call it {Complete?}:

IF({Status} = "Complete", 1)

In your Projects table, create a Count field, let’s call it {Total Tasks}, that counts the total number of linked Tasks.
In your Projects table, create a Rollup field, let’s call it {% Complete}, that rolls up like this:

Table = Tasks
Field = {Complete?}
Agg Function = SUM(values) / {Total Tasks}

Format that Rollup field as a Percentage field in the “Formatting” menu.

(Now just convert terms to whatever your schema is… “Tasks” = “Responses”, “Projects” = ???)

2 Likes

Perfect! Thank you.

So how do I wrap that to remove the NaN in fields that are missing data.

Which field is returning the NaN? I’m not getting any NaN’s in my implementation

I get it in my % field if the count has no records completed.

image

image

I don’t get that in mine… I just get a “0%” value; not sure what we are doing differently from eachother