CountIf Parent Hierarchy Column and Secondary Column

kdf3

I apologize if this has been asked. I am trying to figure out a Countif formula combined 2 columns.

Column 1 is a hierarchy level column in 5 levels (0=great-grandparent, 1=grandparent, 2=grandparent, 3=child, 4=child).

Column 2 is a internal priority level with 4 levels (High, Medium, Low, N/A).

I want to get count of all the levels at the child level but in groups (how many high, how many medium and so forth.

I have only figured out how to do the grand count total for all in Column 1

=COUNTIF([Column 1]1:[Column 1]308, 3) = 70.

But how do I do the combined CountIF?

In English it would say, if Column 1 is equal to 3, then count "High" in Column 2.

I would have 4 of these formlas (for each level) and these 4 should equal 70.

Make sense?

Mike Wilday

Try using a Countifs formula. https://help.smartsheet.com/function/countifs

=countifs([Column 1]1:[Column 1]308, 3, [Column 2]1:[Column 2]308, "High")

The countifs formula is looking for multiple criterion. Here you are saying that you want column 1 to = 3 and Column 2 to equal high to count the item. 

kdf3

This worked. Thank you.

 

How do I add an OR to it? I have decided to add a third criteria and can't figure what I am doing wrong. I referred to some other formulas, and thought I matched it but it brings it back as UNPARSEABLE.

 

=COUNTIFS ([#Ancestor]:[#Ancestor], 3, Level:Level, "AAA", Greenlit:Greenlit, OR("Green", "Yellow"))

I also tried

=COUNTIFS ([#Ancestor]:[#Ancestor], 3, Level:Level, "AAA", Greenlit:Greenlit, OR(@cell="Green", @cell= "Yellow"))

 

If I was to run it as a filter it would read:

If #Ancestor = 3

If Level = AAA

If Greenlight = Green or Yellow

Then show result. 

The count should show 12. Basically I don't want to show / count anything that is a Red or Blue status but if I do a range, if I resort it keeps it in the sort. (ie if I move something from Green Status to Red Status I don't want to count it but if it's in the range, it retains the range - does that make sense?).

kdf3

I think I figure it out. This seemed to work. I think the space after COUNTIFS was the problem. But this seems to work.

=COUNTIFS([#Ancestor]:[#Ancestor], 3, Level:Level, "AAA", Greenlit:Greenlit, OR(@cell = "Green", @cell = "Yellow"))

Mike Wilday

Nice... using the @cell... If that works, great. I have had a TERRIBLE time getting OR statements to work inside Count or Sum IFS. I usually do the formula for one, + the formula for the other... so in your case it would be... 

=COUNTIFS ([#Ancestor]:[#Ancestor], 3, Level:Level, "AAA", Greenlit:Greenlit, "Green")+COUNTIFS ([#Ancestor]:[#Ancestor], 3, Level:Level, "AAA", Greenlit:Greenlit, "Yellow")