Add three criterion -one with a checkbox

Carrie Tortorella

Hello! I can combine two criterion such as a date deadline is equal to a month (e.g. April) and if a status is one of the dropdown selections (e.g. Not Started):

=COUNTIFS([Price Sheet Deadline]:[Price Sheet Deadline], IFERROR(MONTH(@cell), 0) = 4, [Round #3 Price Sheet Status]:[Round #3 Price Sheet Status], "Not Started")

But I also need to add a third criterion to check if a column has a checkbox checked with checked = Yes and not checked = No. (e.g. Yes/No, or 0/1) which is a data point if a contract was renewed (=checked) or not yet renewed (=not checked). How would I add that to this formula?

Andrée Starå

Hi @Carrie Tortorella

I hope you're well and safe!

Try something like this.

=COUNTIFS([Price Sheet Deadline]:[Price Sheet Deadline], IFERROR(MONTH(@cell), 0) = 4, [Round #3 Price Sheet Status]:[Round #3 Price Sheet Status], "Not Started",ContractStatus:ContractStatus,1)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Carrie Tortorella

@Andrée Starå Thank you - that worked! Have a great day!

Andrée Starå

@Carrie Tortorella

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Carrie Tortorella

Hello - just let me know if I should start a new thread. I need a distinct count of a fourth field added to this too. Here is the result from above:

=COUNTIFS([Price Sheet Deadline]:[Price Sheet Deadline], IFERROR(MONTH(@cell), 0) = 7, [Round #3 Price Sheet Status]:[Round #3 Price Sheet Status], "Billing Approved", [Account has Updated Renewal Added to SY21-22 TL]:[Account has Updated Renewal Added to SY21-22 TL], 1)

And I want to add a distinct count of Price Sheets. How would I fit this into the above formula? I tried this but it didn't work:

=COUNTIFS(DISTINCT(COLLECT([Price Sheet]:[Price Sheet},[Price Sheet Deadline]:[Price Sheet Deadline], IFERROR(MONTH(@cell), 0) = 7, [Round #3 Price Sheet Status]:[Round #3 Price Sheet Status], "Billing Approved", [Account has Updated Renewal Added to SY21-22 TL]:[Account has Updated Renewal Added to SY21-22 TL], 1))