Countifs for date and contains function

Jennifer Roam

I am trying to write a formula that will count the number of occurrences for specific text on specific dates. I am using two formuals to get information now and I want to aggregate better.

1. Formula to collect count of text: =COUNTIF({Tally Range 1}, CONTAINS(Issue@row, @cell))
   • Drop down multi select whichis why I have to use the contains function.
2. Formula to collect calls per day: =COUNTIF({Tally Range 2}, [Date Received]@row)

I combined the two formulas so I could get a count of how many of the specific issues we seen per day. However, I keep getting an “#Incorect Argument Set" Error. I have tried it multiple ways and keep getting the error. Below is the formula I am using. Any assistance is greatly appreciated.

My formula is: =COUNTIFS({Tally Range 1}, CONTAINS(Issue@row, @cell), {Tally Range 2}, [Date Received]@row).

Jeff Reisman

@Jennifer Roam

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

For functions that take two ranges: The range sizes don’t match for the function.

The function is missing an argument.

There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.

Since it doesn't look like you're missing an argument or have an extra one, check that your ranges are the same size. Generally, when creating a reference to a remote sheet column, I click on the column header to select the entire column. So that's one thing to check.

You mention that you're using CONTAINS because your range is a multi-select dropdown column. Are you familiar with the difference between CONTAINS and the HAS function? CONTAINS searches within a cell to match a text string. So if you search for "apple" and you have cells containing "apple" and "applesauce", CONTAINS will match to both cells. HAS was built for multi-select columns. HAS searches for entire distinct values within a multi-select cell. So if you're looking for "apple" in a multi-select column, where row 1 has values of "apple" and "orange", and row 2 has values of "apple juice" and "orange juice" - HAS will only match with row 1 where it found the distinct value of "apple", whereas CONTAINS would match row 1 and row 2 because it found the string "apple" in both.

Jeff Reisman

@Jennifer Roam

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

For functions that take two ranges: The range sizes don’t match for the function.

The function is missing an argument.

There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.

Since it doesn't look like you're missing an argument or have an extra one, check that your ranges are the same size. Generally, when creating a reference to a remote sheet column, I click on the column header to select the entire column. So that's one thing to check.

You mention that you're using CONTAINS because your range is a multi-select dropdown column. Are you familiar with the difference between CONTAINS and the HAS function? CONTAINS searches within a cell to match a text string. So if you search for "apple" and you have cells containing "apple" and "applesauce", CONTAINS will match to both cells. HAS was built for multi-select columns. HAS searches for entire distinct values within a multi-select cell. So if you're looking for "apple" in a multi-select column, where row 1 has values of "apple" and "orange", and row 2 has values of "apple juice" and "orange juice" - HAS will only match with row 1 where it found the distinct value of "apple", whereas CONTAINS would match row 1 and row 2 because it found the string "apple" in both.

Jennifer Roam

Thank you Jeff. I logged out after submitting this question and when I went to try your fix, it showed the formula working correctly before I even had to update it. Thanks also for the clarification on the difference between CONTAINS and HAS. I had not known that. Again thanks for the help.