COUNTIF formula counting specific criteria under a PARENT row

Bradhickswa

Hi All,

I have a shipping tracker I created for six different stages we're doing for a project. I need to create a graphical representation of the different statuses for each stage and type. The formula would read: Count of "delivered" for "POM to IHA" under "WW01, as one example. A separate formula would be Count of "In Transit" for "IHA to PA" under WW02. I've attached a picture of my table to show you what I'm referring to.

Thanks!

Brad

Darren Mullen

@Bradhickswa Look at using COUNTIF or COUNTIFS with CHILDREN() or DESCENDANTS() as the range in COUNTIF / COUNTIFS.

Function list

Bradhickswa

Hi @Darren Mullen,

Thank you for your response! Could you help me with how I would set up the formula? I tried doing it a number of ways with no positive result. The latest method was : =COUNTIFS({AB Shipping Status}, "In Transit", DESCENDANTS({WW02 Cross-Site} {AB Build Stage}), "POM to IHA")

Darren Mullen

@Bradhickswa In this formula: COUNTIFS({AB Shipping Status}, "In Transit", DESCENDANTS({WW02 Cross-Site} {AB Build Stage}), "POM to IHA"). I see some immediate issues.

1. You are referencing ranges with curly braces indicating they are off sheet references?
2. You can't have 2 references in the Descendants function, and they should not be off-sheet references, you'll need to use an on sheet column in brackets []

Darren Mullen

@Bradhickswa this video I made a while back shows an example ok using the descendants function.

https://youtu.be/Dzo0UYjxMmI