Having issue with COUNTIFS

ChrisUPC

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

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

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

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

@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

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.