How to set up multiple criteria on countifs

hi, I want to set up formula to count ideas that are: 1) Not checked in SCC, 2) Approval Status is not "Cancel/Reject", Portfolio is "ADA/Commerical", but it always gives me count as 1 only. I don't understand it.

This is what I use in filter

This is formula I use:

=COUNTIFS({IDEA Request Sheet Range 1}, =0, {IDEA Request Sheet Range 4}, <>"Cancel/Reject", {IDEA Request Sheet Range 2}, ="ADA/Commercial")

Range 1=Column "Created in SCC"

Range 4=Column "Approval Status"

Range 2=Column "Portfolio"

Thank you

Find more posts tagged with

Formulas

Accepted answers

All comments

Dan Irizarry

Your post doesn't make your objective clear, but have you considered using the sheet summary section to count each criteria individually and then aggregate the sums if necessary?

You can find additional information on sheet summaries here: Define your work with sheet summary | Smartsheet Learning Center

Min Zhang

@Dan Irizarry Sorry, forgot to mention why I need to do this count. I want to build a dashboard with chart to show these counts, the one I gave is just one example of data I want to show in that chart.

But to my knowledge, in order to build the chart, I need to already have the data, meaning the count of these ideas in other sheet. I could have the count by using Summary report, but the dashboard won't be able to use the report to build the chart.

That's why I do this step, after I have all the counts for different criteria, I will be able to build the chart in the dashboard. Thank you

Dan Irizarry

@Min Zhang,

If I'm understanding your objective correctly, there are a couple of ways you can go about achieving this.

Both methods use the Sheet Summary to create the necessary counts.

Creating the sheet summaries:

Column "Created in SCC": Because you appear to be counting unchecked boxes, we need to create a workaround as SmartSheet will count blank rows when using countif on a checkbox column.

Add an additional checkbox column and apply the following formula: =IF([Created in SCC]@row = 1, false, true)

It should look like below and have the opposite option of whatever value is in Created in SCC. (Any checkbox icon is fine, I used flags for legibility)

Next create a sheet summary and apply the following formula: =COUNTIF([SCC_Check]:[SCC_Check], 1)

Note: If you name your check column differently, you will need to use the name you have chosen in the formula instead of SCC_Check

Approval Status: Create a sheet summary and apply the following formula: =COUNTIF([Approval Status]:[Approval Status], <>"Cancel/Reject")

Portfolio: Create a sheet summary and apply the following formula: =COUNTIF(Portfolio:Portfolio, ="ADA/Commercial")

You should now have something that looks like this:

From here we have two options for presenting the values to the dashboard:

Option A: Metric Widget

Add a metric widget to your dashboard, select the sheet with your data, click the Sheet summary data radial, and select the metrics you would like to show.

Option B: Chart Widget

For this option you need to create a report using the sheet summary report.

Select your data sheet and choose all columns you want to surface to your chart.

Create a widget on your dashboard selecting the new summary report and you should be good to go!

Hope this helps!

Min Zhang

Thanks @Dan Irizarry

I actually only want one count, it means I want to see the count that fulfill all the criteria at the same time, not individually.

I want to see the count that fulfills:

1) Created in SCC is unchecked

2) Approval Status<>"Cancel/Reject"

3) Portfolio="ADA/Commerical"

Thank you

Dan Irizarry

@Min Zhang ,

You can achieve the count where all criteria match by combining all statements into a single countifs statement in a sheet summary value.

As previously mentioned, I still recommend a helper column for checking SCC to avoid counting blank cells.

If you are looking to display just the single value you can use the metric widget as illustrated in the previous response.

=COUNTIFS([SCC_Check]:[SCC_Check], 1, [Approval Status]:[Approval Status], <>"Cancel/Reject", [Portfolio]:[Portfolio], ="ADA/Commercial")

Min Zhang

@Dan Irizarry Thanks a lot! It works now. You saved my life.

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.