Count Multiple Values in the Same Column

Almrie217

I'm trying to write a Sheet Summary formula that will tell me how many times multiple criteria appears in one column based on the conditions of another column. I'm struggling with the OR function, and all my COUNTIFS formulas result in 0. I'm using a simple table to help me learn how to write Sheet Summary formulas, but my questions are getting complex. :) What is the best function to answer the question "How many Mugs and Cups are in the Production Stage DELIVERED or PRODUCTION?" The answer should be 9, but I keep getting 0, unparseable, or a invalid data type, I think because I don't fully understand how to format COUNTIFS with OR.

Here is where I tried to keep it simple: "Count if Production Stage is DELIVERED or PRODUCTION." The answer should be 12, but I get an invalid data type.

The sheet summary fields are all Text/Number, and the Column Type is Dropdown. How would the function change if a column was Multi-Select?

Melissa Yamada

Hello @Almrie217

You're almost there! You're just missing some formula logic on your OR statement using "@cell".

Try this:

=COUNTIFS([PRODUCTION STAGE]:[PRODUCTION STAGE],OR(@cell="DELIVERED",@cell="PRODUCTION"),TYPE:TYPE,OR(@cell="Mug",@cell="Cup"))

If you'll be using a multi-select dropdown column, you need to utilize the HAS function.

=COUNTIFS([PRODUCTION STAGE]:[PRODUCTION STAGE],OR(@cell="DELIVERED",@cell="PRODUCTION"),TYPE:TYPE,OR(HAS(@cell,"Mug"),HAS(@cell,"Cup")))

Note that the HAS function checks if the cell has that value selected. So for example, you have 3 rows that has either "Mug", "Cup", or both "Mug" and "Cup" in a cell. It will not count as 2 values when a cell contains both Mug and Cup. I hope I makes sense.