Excel - Calculating quantities in a hierarchy

[mmccoog]

Hello,

 

[Sheeloo]

Your question is not clear, however
Assuming your Levels are in Col A, indicated by 1,2,3,4
Col B indicates whether it is a cost by "C" and Sum by "S"
and you have acutal values in Col C,
then you can sum up cost at a given level
=SUMPRODUCT(--(A1:A100=D1),--(B1:B100="C"),(C1:C100))

where D1 contains the level (1,2 ,3 or 4)

If you want to sum level 3 AND 4 then you can have
=SUMPRODUCT(--(A1:A100>D1),--(B1:B100="C"),(C1:C100))
with D2 having the value 2

 

[mmccoog]

Thank you Sheeloo. That was extremely helpful. The first example was exactly
what I was looking for. The only problem is, I want the sum function to stop
when it hits a level that is equal or higher than the level on the current
line. Is that possible?

 

[Sheeloo]

If you have cost for two products and you want to sum for levels separately
for product A and B then you have to introduce the Col for product also in
the SUMPRODUCT formula...

Once you udnerstand the formula then you can build it yourself

=SUMPRODUCT(--(A1:A100>D1),--(B1:B100="C"),(C1:C100))
what the above does is find the rows where cell value in Col A is greater
than D1 AND cell value in Col B is equal to "C" and sum the values in Col C
for those rows...
A1:A100>D1 give your TRUE or FALSE. -- in front converts them to 1 and 0...
Essentailly SUMPRODUCT multiplies the values it gets for each set within it...

Since you get 0 when conditions are not met 0 is added for those rows and
acutal values for others.

Hope this makes sense..

Let me know if you need further help.

 

[mmccoog]

Sorry, I meant I want it to stop when it hits a level that is equal or lower
than the current line. So when it's on the first level 1, it will sum all the
level 2 lines but stop when it gets to the next level 1. Does that make sense?

 

[Sheeloo]

Which level is highest 1 or 4?
Do you have level 1s first then level 2s or you have level 1 then level2,..
and then this pattern repeats...?

You can adjust your range and/or conditions to get what you want. If you can
tell us in detail then we can provide a formula.

 

[mmccoog]

The file starts at level 1 and can go down as low as level 7, then another
level 1. Something like this:
1
2
3
3
2
3
4
1
2
3