Count formula - If two columns equal different values

Hello,

I'm trying to use a sheet summary formula to count how many times the "Status" column equals "Complete" AND the "Level" column equals "1". I've used several iterations of a formula and continue to get errors. Can someone help?

This is one of the formulas I tried:

=COUNTIFS({Status:Status}, CONTAINS("Complete", @cell ), {Level:Level}, CONTAINS("1", @cell ))

I got "#INVALID REF.

Thanks,

Angie

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

That error comes from not setting up a {Cross Sheet Reference} properly. Try following the below linked instructions for creating these, and this formula:

=COUNTIFS({Source Sheet Status Column}, @cell = "Complete", {Source Sheet Level Column}, @cell = 1)

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

AngieChristophersen

Thanks Paul! I'm not pulling data from any other sheets. I want this sheet summary to show me totals for the Status column equaling Complete and the Level equaling 1, both columns within the same sheet. Would I still use this type of formula you provided?

Paul Newcome

In that case, remove the {Curly Brackets} from around each of your ranges.

AngieChristophersen

I think we're getting somewhere, the formula didn't produce an error; however, it didn't properly calculate.

I used this: =COUNTIFS(Status:Status, CONTAINS("Complete", @cell ), Level:Level, CONTAINS("1", @cell ))

The total should equal 1 based on this example:

AngieChristophersen

I got it to work by using:

=COUNTIFS(Status:Status, HAS(@cell , "Complete"), Level:Level, HAS(@cell , "1"))

Paul Newcome

You actually do not need the CONTAINS or the HAS functions at all. Simply using "@cell = x" for each should work.

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.