Formula help with counting columns IF it has value

My bad formula that doesn't work

=countifs({link to a column in another sheet} = "monitoring", AND {link to a column in another sheet} = "in review") AND ({link to a column in another sheet}, "minor")

I essentially want it to count the number of "minor" risks are in "monitoring" OR "in review" from another spreadsheet. I tried to do the range vs entire column and it didn't work (plus MORE rows may be added so if I do a range vs entire column it won't count new ones). One column on that RAID log would be for "monitoring" and "in review" and another column in that same spreadsheet will be for "minor".

This is my bad formula where I counted range

=COUNTIFS({BenAdmin RAID Log Range 1} = "monitoring"), AND({BenAdmin RAID Log Range 1} = "in review"))+{BenAdmin overall impact}, "minor")

Find more posts tagged with

Accepted answers

All comments

Dan Beres

Hi @StaceyRxBenefits

Unfortunately, COUNTIFS really does not like OR statements. It is less clean looking solution, but given the information you have I would try something along the lines of:

=COUNTIFS({BenAdmin RAID Log Range 1}, "monitoring", {BenAdmin overall impact}, "minor") + COUNTIFS({BenAdmin RAID Log Range 1}, "in review", {BenAdmin overall impact}, "minor")

COUNTIFS already has built in AND statements between each range+criterion combo so you don't necessarily need to implement that into the statement. The above formula will take anything that is both "minor" and "monitoring" and add it to anything that is both "minor" and "in review."

Let me know if I can provide any more clarity or if I misunderstood the ask!

StaceyRxBenefits

Thank you for your response. When I add that formula above it shows INCORRECT ARGUEMENT SET I'm not sure what I'm doing wrong - may need to have a help call w/Smartsheet.

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.