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.

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.

Almrie217

Excellent. Thank you, Melissa. That worked! I'm now using the HAS function to separate colors and stages. What a fun little function!

Melissa Yamada

Happy to help! 😊

Almrie217

Stumped again! I'm trying to build upon the lessons above, and I'm not able to get a correct result with the HAS function in a multi-select column, I think because of the counting note above. How would I revise the logic so that it counts "How many green small mugs have been ordered?" The answer is 4, but I keep getting 1 when I try to write the logic "Count if Order Stage is Ordered, Type is Mug - Small, and Color has Green selected in the column." 🤔