Hello,
I have a rather complex question but limited experience with excel. I have a
family tree type of file and need to calculate costs based on the lower level
costs.
Example:
Level 1 AAAA sum of levels 2 ($17)
Level 2 BBBB $5
Level 2 CCCC sum of levels 3 ($12)
Level 3 DDD sum of level 4 ($5)
Level 4 EEE $5
Level 3 FFF $7

Each record has the level code and an indicator to tell if it's a sum or if
it has the cost. I'm able to do this manually but the files can become large
and this takes a while to process, level by level. Hopefully this make sense
and there's an easy way to do this.

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:A100D1),--(B1:B100="C"),(C1:C100))
with D2 having the value 2


"mmccoog" wrote:

Hello,
I have a rather complex question but limited experience with excel. I have a
family tree type of file and need to calculate costs based on the lower level
costs.
Example:
Level 1 AAAA sum of levels 2 ($17)
Level 2 BBBB $5
Level 2 CCCC sum of levels 3 ($12)
Level 3 DDD sum of level 4 ($5)
Level 4 EEE $5
Level 3 FFF $7

Each record has the level code and an indicator to tell if it's a sum or if
it has the cost. I'm able to do this manually but the files can become large
and this takes a while to process, level by level. Hopefully this make sense
and there's an easy way to do this.

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" wrote:

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:A100D1),--(B1:B100="C"),(C1:C100))
with D2 having the value 2


"mmccoog" wrote:

Hello,
I have a rather complex question but limited experience with excel. I have a
family tree type of file and need to calculate costs based on the lower level
costs.
Example:
Level 1 AAAA sum of levels 2 ($17)
Level 2 BBBB $5
Level 2 CCCC sum of levels 3 ($12)
Level 3 DDD sum of level 4 ($5)
Level 4 EEE $5
Level 3 FFF $7

Each record has the level code and an indicator to tell if it's a sum or if
it has the cost. I'm able to do this manually but the files can become large
and this takes a while to process, level by level. Hopefully this make sense
and there's an easy way to do this.

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:A100D1),--(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:A100D1 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" wrote:

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" wrote:

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:A100D1),--(B1:B100="C"),(C1:C100))
with D2 having the value 2


"mmccoog" wrote:

Hello,
I have a rather complex question but limited experience with excel. I have a
family tree type of file and need to calculate costs based on the lower level
costs.
Example:
Level 1 AAAA sum of levels 2 ($17)
Level 2 BBBB $5
Level 2 CCCC sum of levels 3 ($12)
Level 3 DDD sum of level 4 ($5)
Level 4 EEE $5
Level 3 FFF $7

Each record has the level code and an indicator to tell if it's a sum or if
it has the cost. I'm able to do this manually but the files can become large
and this takes a while to process, level by level. Hopefully this make sense
and there's an easy way to do this.

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" wrote:

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:A100D1),--(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:A100D1 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" wrote:

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" wrote:

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:A100D1),--(B1:B100="C"),(C1:C100))
with D2 having the value 2


"mmccoog" wrote:

Hello,
I have a rather complex question but limited experience with excel. I have a
family tree type of file and need to calculate costs based on the lower level
costs.
Example:
Level 1 AAAA sum of levels 2 ($17)
Level 2 BBBB $5
Level 2 CCCC sum of levels 3 ($12)
Level 3 DDD sum of level 4 ($5)
Level 4 EEE $5
Level 3 FFF $7

Each record has the level code and an indicator to tell if it's a sum or if
it has the cost. I'm able to do this manually but the files can become large
and this takes a while to process, level by level. Hopefully this make sense
and there's an easy way to do this.

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" wrote:

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" wrote:

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:A100D1),--(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:A100D1 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" wrote:

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" wrote:

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:A100D1),--(B1:B100="C"),(C1:C100))
with D2 having the value 2


"mmccoog" wrote:

Hello,
I have a rather complex question but limited experience with excel. I have a
family tree type of file and need to calculate costs based on the lower level
costs.
Example:
Level 1 AAAA sum of levels 2 ($17)
Level 2 BBBB $5
Level 2 CCCC sum of levels 3 ($12)
Level 3 DDD sum of level 4 ($5)
Level 4 EEE $5
Level 3 FFF $7

Each record has the level code and an indicator to tell if it's a sum or if
it has the cost. I'm able to do this manually but the files can become large
and this takes a while to process, level by level. Hopefully this make sense
and there's an easy way to do this.

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

"Sheeloo" wrote:

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" wrote:

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" wrote:

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:A100D1),--(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:A100D1 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" wrote:

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" wrote:

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:A100D1),--(B1:B100="C"),(C1:C100))
with D2 having the value 2


"mmccoog" wrote:

Hello,
I have a rather complex question but limited experience with excel. I have a
family tree type of file and need to calculate costs based on the lower level
costs.
Example:
Level 1 AAAA sum of levels 2 ($17)
Level 2 BBBB $5
Level 2 CCCC sum of levels 3 ($12)
Level 3 DDD sum of level 4 ($5)
Level 4 EEE $5
Level 3 FFF $7

Each record has the level code and an indicator to tell if it's a sum or if
it has the cost. I'm able to do this manually but the files can become large
and this takes a while to process, level by level. Hopefully this make sense
and there's an easy way to do this.