COUNTIF() Formula Translation to Airtable Help

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