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 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!