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?

Find more posts tagged with

Formulas

Marketing

Media & Entertainment

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…