Need help Summary Formula Countif multiple criteria

Have a formula - Issue is that the fit/gap can be one or the other not both

=(Countif([Client Final Approval]:[Client Final Approval], “Needs Review”))+ (Countif([Fit/Gap]:[Fit/Gap], “Gap – In Scope”)) + (Countif([Fit/Gap]:[Fit/Gap], “Fit – In Scope”))

Find more posts tagged with

Accepted answers

Darren Mullen

@Meg Y

You're capturing rows that meet all of that criteria and adding them together.

Maybe you are trying to combine the Client Final Approval with the Fit/Gap criterion?

If so, use CountIFS()

Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Gap – In Scope”) + Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Fit – In Scope”)

ALTERNATIVELY

Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], or(@cell = “Gap – In Scope”, @cell = “Fit – In Scope”))

All comments

Darren Mullen

@Meg Y

You're capturing rows that meet all of that criteria and adding them together.

Maybe you are trying to combine the Client Final Approval with the Fit/Gap criterion?

If so, use CountIFS()

ALTERNATIVELY

Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], or(@cell = “Gap – In Scope”, @cell = “Fit – In Scope”))

jmyzk_cloudsmart_jp

Hi @Meg Y

You can use the OR(@cell=A,@cell=B) format.

=COUNTIFS([Client Final Approval]:[Client Final Approval], "Needs Review", [Fit/Gap]:[Fit/Gap], OR(@cell = "Gap – In Scope", @cell = "Fit – In Scope"))

Alternatively, you can use a helper column, as shown in the image below.

=OR([Fit/Gap]@row = "Gap – In Scope", [Fit/Gap]@row = "Fit – In Scope")

https://app.smartsheet.com/b/publish?EQBCT=0e1e92869cf34b55a291fcd5dece33c3

Meg Y

Both columns are dropdowns. The formula is also for gap - out of scope and fit - out of scope which are also in the fit/gap column

Client final approval dropdowns are Needs Review and Approved

The numbers are four different widgets on the dashboard. Goal is use the sheet summary.

Meg Y

@Darren Mullen Thank you this formula works. Figured out that one of my criteria was typed incorrectly. Fixed that and viola

=COUNTIFS([Client Final Approval]:[Client Final Approval], "Needs Review", [Fit/Gap]:[Fit/Gap], OR(@cell = "Gap – In Scope", @cell = "Fit – In Scope"))

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.