Summarize two columns

I'm trying to count the # of instances based on criteria from two columns. I must be missing something from my formula. I'm trying to count the total how many times "SRC" objectives there are that do not have a status of Blue. So, I want it to count statuses with "Red, Green, and yellow" where the business vertical column contains "SRC" in the same row.

=COUNTIF([Business Vertical]11:[Business Vertical]80, HAS(@cell, "SRC") + COUNTIF(Status11:Status90, NOT(@cell, "blue")))

Find more posts tagged with

Formulas

Accepted answers

Leibel S

@Shane Sorensen

2 Things:

When entering multiple criteria you need to use COUNTIFS
In your formula the status range was a different size then the Business Vertical range.

See below corrected formula:

=COUNTIFS([Business Vertical]11:[Business Vertical]90, HAS(@cell, "SRC"), Status11:Status90, NOT(@cell = "Blue"))

All comments

Paul Newcome

Your ranges should be the same size. The [Business Vertical] range goes to row 80, but the Status range goes to row 90.

Leibel S

@Shane Sorensen

2 Things:

When entering multiple criteria you need to use COUNTIFS
In your formula the status range was a different size then the Business Vertical range.

See below corrected formula:

=COUNTIFS([Business Vertical]11:[Business Vertical]90, HAS(@cell, "SRC"), Status11:Status90, NOT(@cell = "Blue"))

Paul Newcome

I completely misread the original formula. While you will need the COUNTIFS as mentioned above to accomplish what you are wanting to do, the problem with the original formula is the parenthesis. The first COUNTIF isn't closed off before adding the second COUNTIF.

MrDramaFree

Thank you both. @Leibel S the formula worked perfectly and I ensured ranges were the same. Thanks @Paul Newcome

Paul Newcome

Happy to help. 👍️

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.