Countifs Formula is returning the incorrect Amount.
I am using these three countifs formulas referencing multiple columns in another sheet and it is returning with the incorrect results. What I am doing wrong?
=COUNTIFS({TE Team}, "PME", {Status}, "Active", {Health}, "Green")
=COUNTIFS({TE Team}, "PME", {Status}, "Active", {Health}, "Yellow")
=COUNTIFS({TE Team}, "PME", {Status}, "Active", {Health}, "Red")
I think the problem is because the TE Team Column has multiple values. So if the team has PME and PI, for example, it doesn't count that. Do I need to add the multiple combinations to the formula?
Best Answers
-
It sounds like you just need to incorporate the HAS function into your statements! HAS will search a multi-select cell to see if that one value is present, either with other values or on its own.
Try this:
=COUNTIFS({TE Team}, HAS(@cell, "PME"), {Status}, "Active", {Health}, "Green")
Let me know if this makes sense or if I can clarify anything further.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
No problem at all! Multi-select columns can be tricky in formulas. I'm glad you were able to get it working.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Cynthia Hoyos
Hope you are fine, i checked the formula and its work
=COUNTIFS({TE Team}, "PME", {Status}, "Active", {Health}, "Green")
Maybe you need to check the column name and the reference.
Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@Bassam Khalil I found the problem. the TE Team column might have multiple value check. So if there is a combination of PME and another team such as RPA, it doesn't include it in the account. In that case, do you think that I would need to add the multiple combinations possible, like for example, "CI and PME" and "PME and RPA" so that if it finds them it counts them as well?
-
Please define for me the criteria for the case you want to count and i will design the formula for you using this criteria, and it's better if you can share me as an admin on a copy of your sheets (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
It sounds like you just need to incorporate the HAS function into your statements! HAS will search a multi-select cell to see if that one value is present, either with other values or on its own.
Try this:
=COUNTIFS({TE Team}, HAS(@cell, "PME"), {Status}, "Active", {Health}, "Green")
Let me know if this makes sense or if I can clarify anything further.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Genevieve P @Bassam Khalil Here are the values in the TE Team Column. This could have multiple combinations of PME not just PME alone. I need to be able to include in the count any combination from column. At the moment is only counting the PME value only.
-
Yes, exactly! 🙂
When you list a criteria in a COUNTIFS like this: {TE Team}, "PME"
It searches only for the exact match in quotes, meaning only if that one value is selected. It will ignore any cell that has both "PME" and another value, since that would be "PME, CI", not just "PME".
However the HAS function is built specifically for multi-select columns. It can search a cell to see if that cell has your criteria along with other selections. So a HAS function would read a cell that has both "PME" and "CI" and count this as 1 for PME.
Did you try adding it in?
=COUNTIFS({TE Team}, HAS(@cell, "PME"), {Status}, "Active", {Health}, "Green")
Are you still getting an incorrect result?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P After your second explanation, I got it. This worked!!! Thank you so much.
-
No problem at all! Multi-select columns can be tricky in formulas. I'm glad you were able to get it working.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!