Excel - Calculating quantities in a hierarchy

  • Thread starter Thread starter mmccoog
  • Start date Start date
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
 
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?
 
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.
 
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?
 
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.
 
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
 
Back
Top