Having issue with COUNTIFS

I am having difficulty with a COUNTIFS statement.

I am trying to get a count of parts from an "INTAKE" sheet containing current orders and the parts associated with them. The "INTAKE" sheet has all of the order data listed by row including the ranges I want to count. One range contains multiple (60+) columns with parts ordered AND the other range is a single column with the stage of production from that sheet.

MULTIPLE COLUMNS SINGLE COLUMN The formula is: =COUNTIFS({UPC Parks - Total Ordered}, Part@row, {UPC Parks - Stage}, "TO BE CAST")

I broke it down into singular COUNTIF statements and they works if it only has one condition, however as soon as I add the second condition, it returns #INCORRECT ARGUMENT.

I have also tried: =COUNTIF({UPC Parks - Total Ordered}, AND({UPC Parks - Total Ordered} = Part@row, {UPC Parks - Stage}))

Any help would be appreciated!!!

Answers

  • Ipshita
    Ipshita ✭✭✭✭✭✭

    Hello @ChrisUPC , COUNTIFS definitely work with more than one condition - here is an example,

    =COUNTIFS(Health:Health, "Yellow", Hierarchy:Hierarchy, =3), but in this case, the text/number inside the double quotes have to be an exact match.

    Try this - remove the AND before ({UPC Parks - Total Ordered} and change the { to [

    so your formula becomes - COUNTIFS([UPC Parks - Total Ordered]:[UPC Parks - Total Ordered], Part@row, [UPC Parks - Stage])

    Is your "TO BE CAST" an exact option in that particular column? What about Part, do you have more than one options in the PART? Try using the above example I shared and let me know what you get.

    Cheers!!

    Ipshita Mukherjee

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are definitely going to need a COUNTIFS with multiple range/criteria sets. The problem is that your ranges do not match in shape/size. If a range is a single column, then all ranges need to be a single column. If one range is 60 columns, then the other ranges need to be a single column.


    My suggestion would be a helper column on the source sheet that combines all 60 of those columns into a text string and then running your COUNTIFS with a CONTAINS function on the helper column.


    =COUNTIFS({Helper Column}, CONTAINS(Part@row, @cell), {UPC Parks - Stage}, "TO BE CAST")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • ChrisUPC
    ChrisUPC ✭✭✭✭

    @Paul Newcome

    How do I create the text string column? Is is as simple as column1@row+column2@row etc.? Or does the text need to be separated by commas or spaces? Also, will this work if multiple columns have the same text (part name)?

  • ChrisUPC
    ChrisUPC ✭✭✭✭

    @Paul Newcome Also the what does the @cell refer to? is that the range to search or does it need to be a specific cell?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If the columns are all next to each other, you can use a JOIN function.

    =JOIN([Column1]@row:[Column60]@row, ",")

    I do suggest using a delimiter. The above uses a comma. It doesn't have to look pretty as long as it functions. You can even hide the helper column after setting it up if you wanted to.


    @cell simply tells the function to evaluate the previously established range on a cell by cell basis. You would leave this exactly as is.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!