Countifs for date and contains function

Options
Jennifer Roam
Jennifer Roam ✭✭✭
edited 03/09/23 in Formulas and Functions

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

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jennifer Roam
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!