Adding criteria from multiple columns in a referenced sheet



I'm still fairly new to functions but I'm trying to create a function that will provide a count based off criteria for multiple columns in one sheet. I know that I need to choose "reference another sheet" for each column but I cannot seem to get the function correct. I've tried almost every combination but basically I have a set of criteria for the first column and a set of criteria for the second column and based off of that I want a number to produce.

Here's the latest that I've tried.

=COUNTIF({Hierarchy}, 1, [{Status}, Complete])

I'm needing the sum of the parent (1) from the hierarchy column ADDED to the sum of In Progress cells in the Status column.



  • Devin Lee
    Devin Lee ✭✭✭✭✭

    A little unclear if you are trying to count or sum the values so here is both. You will need an identifier column for the Hierarchy Level if you don't already have one.

    Hierarchy Level Formula


    Count/Sum Formulas

    =count(collect({Hierarchy},{Hierarchy Level},1))+count(collect({Status},{Status},"Completed"))

    =sum(collect({Hierarchy Values},{Hierarchy Level},1))+sum(collect({Status Values},{Status},"Complete"))

  • d_prettyman0414

    These aren't seeming to work. This one "=count(collect({Hierarchy},{Hierarchy Level},1))+count(collect({Status},{Status},"Completed"))" returns a value but it's not correct. I've tweaked the formula to "=COUNT(COLLECT({Hierarchy}, {Hierarchy}, 1)) + COUNT(COLLECT({Status}, {Status}, "Complete"))" and it's bringing back the total for all columns combined with the criteria, not the two criteria considered.

    I'm trying to count the number of 1's in the hierarchy column that are also noted to be complete in the status column.

    Another way it could be done is to count the number of parents (as the number 1 is representing the parent) at the hierarchy level that are in a complete status.

    Thank you so much for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!