Help

Re: COUNTIF() Formula Translation to Airtable Help

958 0
cancel
Showing results for 
Search instead for 
Did you mean: 
GjusD
4 - Data Explorer
4 - Data Explorer

How can I translate the excel formula listed below into Airtable? I don’t see a COUNTIF or SUMPRODUCT fx option. I am trying to calculate the % a project is completed based on dates in multiple columns. Below is how I did it in excel:

=IF(AY2="",COUNTIF(AS2:AX2,"<="&TODAY())*0.1667,COUNTIF(AS2:AY2,"<="&TODAY())*0.1429)

Any help would be greatly appreciated!

1 Reply 1

Welcome to the community, @GjusD! :grinning_face_with_big_eyes: Sorry that this has gone unanswered for so long. :frowning:

Airtable doesn’t currently have an equivalent function to Excel’s COUNTIF. To pull this off, you could either create one massive formula that checks the dates and does the appropriate math, or use a series of helper formula fields (one for each date field) to make the date comparisons, and then another formula field that collects and processes all of those results to create your final percentage. While I’m not afraid of massive formulas, I’d probably lean toward the multi-field setup in this case.

Using your existing column names as Airtable field names, the setup would go like this. Taking {AS2} as an example, you would make an adjacent formula field named something like {AS2D} (adding “D” for “Done”) using this formula:

AND(AS2, AS2 <= TODAY()) * IF(AY2, .1429, .1667)

The first part will return a 1 if AS2 is before today, and a 0 if not, but defaults to 0 if AS2 doesn’t exist. The second part changes the multiplier depending on whether or not AY2 has a date.

Duplicate that field and change its name and formula to make helper fields {AT2D} through {AX2D}. {AY2D} would be slightly different because it’s only checking for its own existence and date comparison:

AND(AY2, AY2 <= TODAY())  * .1429 

Then make your final formula field (I’m calling this {Percent Complete}) something like this:

SUM(AS2D, AT2D, AU2D, AV2D, AW2D, AX2D, AY2D)

Change the formatting option in the field preferences to Percent, and you’re done (minus hiding the helper fields, which I didn’t do for the sake of this demo).

Screen Shot 2019-11-03 at 8.45.24 AM.png