Invalid Argument with COUNTIFS formula


I have a form created to capture checklist Yes/No items for safety observations. There are several questions that are grouped by type (i.e. General, Electric, etc.). I have figured out how to use the COUNTIF formula to count the "No" answers for each group of questions to establish where the risks are taking place:

=COUNTIF([Workplace is clean & orderly (GS1)]:[PPE expectations being followed (GS8)], "❌ No")

But now I want to add in additional criteria to factor into this formula, specifically by Location and by Month, so we can trend risk areas month-to-month for each site. I tried the COUNTIFS formula, but keep getting Invalid Argument:

=COUNTIFS([Workplace is clean & orderly (GS1)]:[PPE expectations being followed (GS8)], "❌ No", [Location]:[Location], "Location 1", [Month]:[Month], "April")

Any ideas on where I am going wrong? Thanks in advance!


  • Adam Murphy
    Adam Murphy ✭✭✭✭

    Formula looks correct. What are the values in those 2 fields? for the Month field, is it actually displaying "April"? Are either of those generated by a formula? If so you may need to use VALUE(@cell) = "April", for example.

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @KarenTF,

    Just to confirm, in the COUNTIFS you want something to meet all the specific criteria before being counted correct?

    The issue is (and honestly I didn't know this until I was trying to solve this question), you are using different size cell ranges for the different criteria (in this case, you're using two columns for the first range but then only one column for the rest). You'll just need to break out the first range into it's own separate range/criteria grouping and that should do it!

    =COUNTIFS([Workplace is clean & orderly (GS1)]:[Workplace is clean & orderly (GS1)], "No", [PPE expectations being followed (GS8)]:[PPE expectations being followed (GS8)], "No", Location:Location, "Location", Month:Month, "April")

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!