Formula Question

Maggie Lehman

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

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