Formula to calculate total sum from a hierarchy column criteria

jhorvath

I have the following sheet that I want to calculate the sum of "Number of slides" which appears in the hierarchy translation column. I've done this same thing with only counting the instances instead of summing them, but I can't get the formula to work for the sumif in the same way that the countif formula worked before. I also need to calculate the sum per date.

James Keuning

Can you create some more records to show how date will be associated with the numbers that you want to sum?

If I was solving this problem with the data you've provided, I would do something like:

Create a column called Hierarchy Group and put this in it:

=IF(COUNT(ANCESTORS(Hierachy@row)) = 0, Hierachy@row, INDEX(ANCESTORS(Hierachy@row), 1))

(That finds the top level parent for every record)

Then put this formula in the cell where you have your formula:

=SUMIFS(instance:instance, [hierarchy translation]:[hierarchy translation], "Number of slides", [Hierarchy group]:[Hierarchy group], [Hierarchy group]@row)

That will get you the number of slides, but I don't think it's what you want. There is something about the "per date" part of the question that does not seem to work.

jhorvath

@James Keuning Thank you!

Here is the sheet where I have almost the same thing. It counts the number of rows that are identified as "Block ID":

In the first case, I want to do mostly the same thing but sum the number of slides under each date. So if I add row 4 to the first sheet that is hierarchy 2, number of slides, and the quantity is 5, the total per date for 4/28/22 is 7.