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

Options

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

  • Max Essig
    Max Essig ✭✭
    Answer βœ“
    Options

    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

  • Max Essig
    Max Essig ✭✭
    Answer βœ“
    Options

    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.
    Genevieve P. Employee Admin
    Options

    Hey @Max Essig

    Thanks so much for posting your solution! I'm glad you were able to figure this out πŸ™‚

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!