COUNTIF functions work separately, but not when combined into a COUNTIFS function

Max Essig

Hello,

I have a situation where I have two working COUNTIF functions, but when I try to combine them into criteria for a COUNTIFS function, I get the error "INCORRECT ARGUMENT SET". Trying to figure out why the criteria don't work when combined into a single function. I want to count the rows where both criteria are met. The references are all in the same sheet.

Here are my working COUNTIF formulas:

=COUNTIF([Carrots]:[Potatoes], FIND([Entry]@row, @cell) > 0)

=COUNTIF(Date:Date, [Search Date]@row)

And here is the failing COUNTIFS formula:

=COUNTIFS(Date:Date, [Search Date]@row, [Carrots]:[Potatoes], FIND([Entry]@row, @cell) > 0)

Help is appreciated, thanks in advance!

Max Essig

Update: Problem solved 🎉

I did some research and found that this is caused by the difference in size between the two ranges. Since Date:Date is one column, but [Carrots]:[Potatoes] is several columns, the COUNTIFS() formula doesn't work.

I was able to overcome this by joining all of the values in [Carrots]:[Potatoes] into a new column, [Vegetables], via a JOIN() function.

Then, I altered the criteria to be CONTAINS() instead of FIND(), searching for the presence of Entry@row within the new [Vegetables] column.

The new, working COUNTIFS() formula looks like this:

=COUNTIFS(Date:Date, [Search Date]@row, [Vegetables]:[Vegetables], CONTAINS([Entry]@row, @cell))

Max Essig

Update: Problem solved 🎉

I did some research and found that this is caused by the difference in size between the two ranges. Since Date:Date is one column, but [Carrots]:[Potatoes] is several columns, the COUNTIFS() formula doesn't work.

I was able to overcome this by joining all of the values in [Carrots]:[Potatoes] into a new column, [Vegetables], via a JOIN() function.

Then, I altered the criteria to be CONTAINS() instead of FIND(), searching for the presence of Entry@row within the new [Vegetables] column.

The new, working COUNTIFS() formula looks like this:

=COUNTIFS(Date:Date, [Search Date]@row, [Vegetables]:[Vegetables], CONTAINS([Entry]@row, @cell))

Genevieve P.

Hey @Max Essig

Thanks so much for posting your solution! I'm glad you were able to figure this out 🙂