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

I have a nested IF formula that returns several different outputs (emojis) depending on different conditions (mostly date comparisons). In a different table i want to rollup only one specific output from this formula with a COUNTIF. This COUNTIF would also later be lookuped to an additional different table. I’ve managed to do a workaround by adding an additional formula column which checks for that specific output and returns 1 or 0 which i rollup with a normal COUNT instead. It works but would be smoother and less columns with a COUNTIF that could rollup that specific output.

We need some formulas which can collect the aggregate value of a field to either the table or group level.

For example;

I have a field “Sale Amount”, I’d like to create another field which is “% of Total Sales”. To achieve this currently requires an absurdly tedious system of roll-ups.

With aggregate formulas this could be done with a single field.

Example:

SUM(Field, [Table/View/Group]) which would return the sum of the field for all records. The optional second parameter could restrict the set of summed records to be either the entire table regardless of view (default option), just the records in the current view, or just the records in the current group, respectively.

SUMIFS(SumField, Condition Field, Condition, [Table/View/Group]) would work the same way, but Condition Field is the name of the field which contains the value we check and Condition might be some list of operators (Equal, Not Equal, Greater Than, etc.)

Hi @Jeremy_Oglesby. Given that the formula is still not there, I assume COUNTIF is not yet implemented. One of the examples I would like to use COUNTIF and COUNTIFS is in my opportunity pipeline. For instance, if I want to calculate what is the conversion rate of opportunities into “Won closed”, I would use the count if and say,

(Count field “x” if the status in field “y” equals to “Won Closed”) / count the records = conversion rate [formatted as %]

Similarly, I can use the conversation rate but for a specific year. As such, I would like to be able to use the COUNTIFS and say,

(Count field “x” when the status field “y” equals to “Won Closed” and the Year is “2019”)

with countifs, you can add as many different conditions to be met before a count is triggered.

The reason why filters don’t work is that filter reduced down the records when we don’t want to filter down the records.

I hope this gives you a good understanding of the function, its application and importance to have it incorporated into Airtable.