Formula Question

How do I add a second Sub Area to the following formula in a sheet so it accounts data for both Sub-Areas together. The formula I currently have set up is =COUNTIFS({Type-Objective/Activity}, "Objective", {Complete}, "Not Complete", {Workset}, "Workset A", {Sub-Area}, "Curriculum Management", {Sprints}, CONTAINS("Sprint #1", @cell)) , the second sub area I’d like to add is another {Sub-Area} titled SOM – Curriculum Management

To Sum up, I'd like data to be included for both "Curriculum Management" and "SOM - Curriculum Management" sub-areas

Find more posts tagged with

Accepted answers

All comments

Arlene C

I'm not sure if this will help, but when I need two different "counts" I'll add the Countifs for each column I want to search. I find sometimes that's a quicker solution than an OR statement. IE: COUNTIFS(Status:Status, "Approved", [Request Type]:[Request Type], "Capital") + COUNTIFS(Status:Status, "Approved Partial", [Request Type]:[Request Type], "Capital")

Maggie Lehman

So, exactly how would this formula look like using the above suggestion? Can you apply the suggestion to the specific formula?

Genevieve P.

Hi @Maggie Lehman

Arlene is describing creating 2 COUNTIFS statements and adding them together, like so:

=COUNTIFS(formula) + COUNTIFS(formula)

This is also what I would suggest. You already have one working formula:

=COUNTIFS({Type-Objective/Activity}, "Objective", {Complete}, "Not Complete", {Workset}, "Workset A", {Sub-Area}, "Curriculum Management", {Sprints}, CONTAINS("Sprint #1", @cell))

so create the second formula with the second criteria:

COUNTIFS({Type-Objective/Activity}, "Objective", {Complete}, "Not Complete", {Workset}, "Workset A", {Sub-Area}, "SOM - Curriculum Management", {Sprints}, CONTAINS("Sprint #1", @cell))

Then add them together:

=COUNTIFS({Type-Objective/Activity}, "Objective", {Complete}, "Not Complete", {Workset}, "Workset A", {Sub-Area}, "Curriculum Management", {Sprints}, CONTAINS("Sprint #1", @cell)) + COUNTIFS({Type-Objective/Activity}, "Objective", {Complete}, "Not Complete", {Workset}, "Workset A", {Sub-Area}, "SOM - Curriculum Management", {Sprints}, CONTAINS("Sprint #1", @cell))

Cheers,

Genevieve

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.