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

Answers

  • 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")

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!