COUNTIF AND COUNTIFS Formulas

#1

It would be great if the COUNTIF and the COUNTIFS formulas were implemented in Airtable. I use them in most of my excel spreadsheets, and it is a little frustrating not being able to use it here. Are there any plans to add more formulas?

New Formula Field Functions
#2

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

#3

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?

#4

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!

#5

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?

#6

I too would use COUNTIFs

#7

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

#8

+1 !!!
Iām also a frequent user of countif functions in spreadsheets. Please add itā¦

#9

not optimal, but an IF formula field that copies the desired āCOUNTIFā field values combined with a rollup should do the trick, no?

#10

Any news on implementing COUNTIF formula??

#11

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

#12

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

#13

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!

#14

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.

#15

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

#16

I need COUNTIFS as well! Please implement.

#17

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!

#18

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ā).

#19

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.

#20

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.