COUNTIF AND COUNTIFS Formulas

@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” = ???)

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.

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.

COUNTIF please. It would solve a lot of problems and spare the headaches everyone is talking about here. Simple stuff I would think and would keep the bases nice and tidy and interoperable. Thanks.

This functionality would be very useful.

Pretty obvious use cases all over Airtable team… countif is a common spreadsheet function used often for simple data evaluation. You can’t claim to replace these tools if you can’t hang with the basics.

+1
It’s super annoying to have to find a workaround for this.

This is a huge miss. 5 years since the first post for this and still nothing? Frustrating.

COUNTIF is certainly a must. Currently there is no way to count all the occurrences of a single value in the field B through all the values of the field A. Which is quite a frequent need.

Agreed that COUNTIF would be really useful. Kindly add!

I am trying to count all instances across multiple columns for a specific record for a certain word. I do this in excel quite easily with COUNTIF. I will have to scrap using this product for this reason.

any new here? its been 5 years bout window count if not even available for airtable whats happening?

Would love CountIf… any update???

Read all the comments here in such high hopes for an update at the bottom…and nothing :grinning_face_with_sweat:

• 1 from me, would you to track [Person][Date][Changed status] in a given month

Another request for COUNTIF please! We are using Airtable across several organizations to track the progress of a project, completing a survey of historic dance halls in Texas. Every quarter, I need to report the total number of halls that meet each of 7 status conditions. A COUNTIF formula would make this easy – instead, I guess I have to download the spreadsheet out of Airtable to run the calculation every time. Kind of ridiculous when you consider how basic a formula COUNTIF is.

Just signed up to a trial of Airtable to find this thread. I need this simple formula =COUNTIF($H$2:$H,G2) to count how many instances of an affiliate referral code has been used. strange that it’s not possible. But maybe I don’t know how to use the platform properly yet.

I really need to COUNT with different date options. I think COUNTIF should help me. Please add it to Airtable, guys!

Hello! We are working on an extension that will allow you to add COUNTIF & COUNTIFS and other criteria formulas (eg. SUMIF) to Airtable. Our solution is no-code and will take to minutes add to your tables:

Feel free to join our waitlist or send any feedback or questions to hello@supersparks.io .

The best way to get this working natively that I’ve found is to use regex. For example, trying to count all occurrences of Active. It requires , and 1 to not be a part of the text. I only tested this with a Rollup.

Formatted to read it easier:

LEN(
    REGEX_REPLACE(
        REGEX_REPLACE(
            ARRAYJOIN(
                ARRAYCOMPACT(values)
            , ',')
        ,'Active(,|$)','1$1')
    ,'[^1]','')
)

ARRAYCOMPACT - remove empty string and null from values
ARRAYJOIN - join rest of values with comma to have a deliminator
REGEX_REPLACE 2 - replace all occurrences of Active followed by a comma or end of string with 1 followed by a comma or end of string
REGEX_REPLACE 1 - replace anything that isn’t a 1
LEN - return length of string, which is the count of 1s

If REGEX_REPLACE supported it, ideally Active(,|$) would be (?<=^|,)Active(,|$) to enforce the presence of the deliminator or start of string.

A native COUNTIF would be great still.