Invalid Argument with COUNTIFS formula

KarenTF

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

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

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