COUNTIF functions work separately, but not when combined into a COUNTIFS function
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!
Best Answer
-
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))
Answers
-
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))
-
Hey @Max Essig
Thanks so much for posting your solution! I'm glad you were able to figure this out 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!