Countifs Formula is returning the incorrect Amount.

Options
Cynthia Hoyos
Cynthia Hoyos ✭✭
edited 05/25/21 in Formulas and Functions

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

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 05/25/21
    Options

    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.

    PMP Certified

    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"

  • Cynthia Hoyos
    Options

    @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?

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Cynthia Hoyos 

    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.

    PMP Certified

    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"

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Cynthia Hoyos

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Cynthia Hoyos
    Options

    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.


  • Genevieve P.
    Options

    Hi @Cynthia Hoyos

    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?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Cynthia Hoyos
    Options

    @Genevieve P After your second explanation, I got it. This worked!!! Thank you so much.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    No problem at all! Multi-select columns can be tricky in formulas. I'm glad you were able to get it working.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!