COUNTIF AND COUNTIFS Formulas

I’m also a frequent user of countif functions in spreadsheets. This feature in AirTable would be a big help to me.

Thank you for the feedback! We’re definitely planning on adding more formulas—the set of formulas that we have right now certainly isn’t set in stone.

As for COUNTIF, how would you plan on using it within Airtable? Formulas within Airtable work on a field-by-field basis, not a cell-by-cell basis, so naturally any formula implemented within Airtable will have slightly different considerations than implementing a formula in Excel. Why would you need a COUNTIF formula specifically, as opposed to using a view with a set of filters and seeing the number of records within that view?

Thanks for your reply Katherine, the particular Base I’m trying to reproduce from excel in Airtable is for a sheep management operation. I have the main table on which many of my reports i can reproduce with filtered views. One particular field from every record i have not been able to reproduce in airtable and that is the Birth Type. I want airtable to calculate automatically the number of sheep born from the same mother on the same date to determine if it is a single, twin or triplet. In excel i use countifs formula so it counts if it is the same mother and it was born on the same date and it just gives me a 1 a 2 or a 3. I have tried using various tables without any luck on reproducing my result from this excel formula. =IF(COUNTIFS(Mother,Mother,DOB,DOB)=3,“Triplet”,IF(COUNTIFS(Mother,Mother,DOB,DOB)=2,“Twin”,IF(COUNTIFS(Mother,Mother,DOB,DOB)=1,“Single”,"")))
Any help or suggestion would be greatly appreciated!

Here’s a summary of the first situation I encountered where a OOUNTIF function would have been helpful. I’m certainly open to a suggestion about an alternative approach.

I was building a base for tracking job applicants and had a bunch of fields where I was storing scores for individual responses to a set of interview questions. Each interview response is scored as a 0, ? or +. I’d like to have another field where I can generate a total “+” count for all the questions for a particular candidate. I’d like to be able to sort and filter based on that total “score.”

Does that make sense?

I too would use COUNTIFs

Hey, that would be a great feature indeed for reporting purposes. I am using AirTable to catalogue presentations at our events and, specifically, I am currently going through an approval for publishing of their videos. So I would use COUNTIF to have a quick view of how many got approved already, how many are pre-approved, how many pending, how many declined, etc. A workaround would be of course using the filter and see the number of records, but I would have to do so for each value, whereas it would be nice being able to make a “dashboard” with the number of each of those. Likewise for other fields with pre-set values, it would be good to make other reporting sums (e.g. how many presentations we have within a certain theme - being themes pre-set variables)
Cheers,
Vittorio

+1 !!!
I’m also a frequent user of countif functions in spreadsheets. Please add it…

not optimal, but an IF formula field that copies the desired “COUNTIF” field values combined with a rollup should do the trick, no?

Any news on implementing COUNTIF formula??

Hi,

My application for COUNTIF is to count the number of classes (linked date records) attended within the last week. Any ideas for a workaround? I need to COUNT date records that are less than 7 seven days old.

I hope to see this formula function added to Airtable.

Thanks,
Ken

I would love to have a COUNTIF feature for two reasons…

1. I’d like to be able to use it to count the number of active tasks per team member. For example, count the number of cards with status of x. I don’t want to change the view of the board to get this info, I really just want to be able to click on a team member and see that number. Right now my “Active Tasks” equals my “Total Tasks” which is not true.
2. I’d like to see the number of cards which are more than 2 weeks old (I have a field for “Date of Submission” on all cards).
   Thanks,
   Sabrina

I would love CountIfs as well! Especially if I can call from other tables in my base, without having to link records.

I would even be okay if this was a Pro feature!

Also not optimal — and, strictly speaking, both more and less than COUNTIF() — but the routines I outline in this post can provide similar functionality, albeit at a cost.

I’d like to be able to do the exact same use case as #1! Is there any workaround available?

I need COUNTIFS as well! Please implement.

I’m keeping a list of New Formula Field Functions users are requesting here:

And I’d like to add this request to the list. Could you guys help me wrap my head around how a COUNTIF() might work if implemented in Airtable? What parameters would be entered in the parenthesis (mandatory and optional), what would the function do (in Airtable terms, not in spreadsheet terms), and what would it return?

I ask because, as someone who has never used a COUNTIF() in a spreadsheet, I’m not sure how to formulate the request in my list.

Thanks!

Hi Katherine - I have an example of where a COUNTIF would be preferable. My links are people-work items in a many-to-many relation. Each work item has a status against it. I want to count how many items of a given status are against each person. i.e. 'COUNTIF(“Complete”). But due to many-to-many, when I create a view and ‘group by’ people, I get as many groups as I have combinations of people working together on a work item, rather than having a group for 1 person and repeating the rows. I guess this makes sense in an relational world…

TO ALL: one way to do this is using a conditional rollup (can’t include links, google ‘airtable conditional rollup’).

I’d like to be able to use COUNTIF or COUNTIFS in order to know if any values are duplicated on one or more fields. Kind of a workaround for the lack of unique constraints on records in Airtable.

FYI, I tossed together a mini demo base showing how one can use a rollup field with an aggregation formula to emulate some of COUNTIF()'s functionality. In the demo, team members are assigned one or more tasks; each task has one of 6? statuses. The field {# complete} returns how many of that individual’s tasks are marked as having been completed.

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.

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().

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?