Adding criteria from multiple columns in a referenced sheet

d_prettyman0414

Hello,

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.

Thanks!

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

=IF(count(children(Hierarchy@row)>=1,1,0)

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!