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?

Find more posts tagged with

Accepted answers

Genevieve P.

https://community.smartsheet.com/discussion/comment/292788#Comment_292788

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

Genevieve P.

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

All comments

Bassam Khalil

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.

Cynthia Hoyos

@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

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

Genevieve P.

https://community.smartsheet.com/discussion/comment/292788#Comment_292788

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

Cynthia Hoyos

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.

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?

Cynthia Hoyos

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

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.