Calculate Structure QUANTITY

Hi,

Please see attached.
Depending IF Column B="ASM" and ASM Level from Column C, I need formula at E4
Basically is a bill quantity

Thanks all

I see no consistency here.
C5 and C6 are 1, then formula in E will multiply two rows from D.
C7 and C8 are 2, then formula in E will multiply three rows from D.

But then,
C11, C14 and C15 are 2, then formula in E will multiply TWO rows from D, instead of three.

Please define a criterion so a formula or some other solution can be used.

Try in E4 and copy down:

Formula:

Please Login or Register  to view this content.

Again, I see inconsistency in your manual inputs in column M, thus some results differ from yours.
I did it in F4 and down, so you compare with your values in column E.
The formula multiplies D4 by current row value in D and by the number of rows in C up in column D.
File attached. Please run some test and let us know how it goes.

This is not what I am looking.
Please see for example, rows 6,7,8
6 is ASM at level 1
7,8 are under 6 at level 2
so I need d6*d7 and d6*d8
Top D4 (top asm) is always 1 (maybe I shouldn't include at all)
So results in E6,E7,E8 are correct

Originally Posted by Bo_Ry

Please try at E4

=PRODUCT(XLOOKUP(SEQUENCE(MAX(1,C4),,0),C$4:C4/(B$4:B4>""),D$4:D4,,,-1))*D4

Thank you so much Bo.
Thank you again.

Hi,
I need NEW formula at F4 for a NEW value in C (New C = Old C +1)
So 0 is 1, 1 is 2, 2 is 3....
At G, I have C4+1
Thanks

Thank you so much