Count children of similar ancestors in a cross sheet formula

Pasquale

Hello team, I am working with cross sheet formulas, and I have 3 tasks that I'm dealing with:

• - I'm trying to count how many items (children) I have in Q1 for each different group (blue lines);
• - I'd like to count the same on year base
• - How many children with certain data (e.g. not empty or where the country is USA)?
• This is the spreadsheet with the data:

• I tryed with help columns like suggested in similar Q&A, but couldn't solve...
• Can you please help? thanks a lot in advance!!!

Genevieve P.

Hi @Pasquale

Cross sheet formulas won't be able to evaluate hierarchy, or identify what Parent a Child row is underneath. You'd want to set up a Helper Column in this main source sheet that has a very simple column formula, like this:

=PARENT(Matricola@row)

See - PARENT Function and Reference Children, Parents, and Ancestors with Hierarchy Functions

This formula will return the direct Parent row data from the Matricola column into each of its child rows. Once you have that text in your Helper Column, you can use this column to see if the cell CONTAINS "Q1" as the Parent data, and COUNT this in your cross-sheet formula:

=COUNTIFS({Helper Column}, CONTAINS("Q1", @cell))

Then when you want to count for specific year and quarter, you can look for the identical text for that Parent instead of using Contains:

=COUNTIFS({Helper Column}, "Q1 2022")

If you're looking to filter by country, you can write in another Column and Criteria like so:

=COUNTIFS({Helper Column}, "Q1 2022", {Country Column}, "USA")

Where this gets tricky is the Blue Rows, that aren't direct Parents. Do you have anywhere in the sheet where you have the Blue category listed in each row?

Let me know if the above information works for some of the data points you need. You could also create a Report and filter off the helper column to then Group and Summarize, if that would be helpful.

Cheers,

Genevieve