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!

Find more posts tagged with

Accepted answers

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

All comments

Max Essig

Update: Problem solved 🎉

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 🙂

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.