nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Using IF, AND, OR to create a summary function

jmo

Hi team!

I'm trying to create a summary report that counts across 2 columns with various criterion the return a count.

Column 1 (Current status): drop down options - No impact; 75% capacity available; 50% capacity available; 25% capacity available; 0% available

Column 2 (Risk to Company): drop down options - Low risk; Med risk; High risk

So I need a formula that counts Med risk & High risk (column 2) plus has a current status (column 1) of "No impacts".

I can figure out the other counts after that first "status" is calculated.

I struggle with combining COUNTIFS with AND/OR functions.

Any help would be greatly appreciated.

Find more posts tagged with

Accepted answers

Paul Newcome

Try something like this to exclude "Low Risk"...

=COUNTIFS([Current Status]:[Current Status], "No Impact", [Risk to Company]:[Risk to Company], @cell <> "Low Risk")

or this to specify "Med risk" or "High risk"...

=COUNTIFS([Current Status]:[Current Status], "No Impact", [Risk to Company]:[Risk to Company], OR(@cell = "Med Risk", @cell = "High Risk"))

Paul Newcome

Yes. @cell just tells the formula to evaluate the range on a cell by cell basis.

All comments

Paul Newcome

Try something like this to exclude "Low Risk"...

=COUNTIFS([Current Status]:[Current Status], "No Impact", [Risk to Company]:[Risk to Company], @cell <> "Low Risk")

or this to specify "Med risk" or "High risk"...

=COUNTIFS([Current Status]:[Current Status], "No Impact", [Risk to Company]:[Risk to Company], OR(@cell = "Med Risk", @cell = "High Risk"))

jmo

@Paul Newcome - does @cell work in a Summary Report?

Paul Newcome

Yes. @cell just tells the formula to evaluate the range on a cell by cell basis.

jmo

Thanks @Paul Newcome - that worked like a charm!

Paul Newcome

Happy to help! 👍️

Thai La

This post helped me out, thanks guys!

I was trying to report back if a column had either yellow or red status and the formula @cell worked for me.

Thanks,

-Thai

Paul Newcome

@Thai La That's great! 👍️

It is always nice to know that multiple people are able to benefit from a single solution.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.