Calculate Cumulative Value from 3-Tiered Lookup

[A___2000]

hello

hope you are doing fine

please find the below screenshot to understand my requirements

help.PNG


so if we take the 26 days for example it means

8 x 60 = 480
15 x 120 = 1800
3 x 200 = 600

total amount : 2880

MOD EDIT: I have changed your title for you. Please read the forum rules ASAP.

2022.xlsx

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[A___2000]

i am sorry,

i just attached the workbook In the post

thanks

[XLent]

one option

Please Login or Register  to view this content.

[A___2000]

thank you so much,

its the perfect formula for me


I added one more row and I couldn't do it myself, can you please help

workbook for your reference.xlsx

one more row.PNG

[XLent]

you just need to adjust the multi-row ranges such that they include an additional row of data

=SUMPRODUCT(--($B$16>=$B$9:$B$12),1+$B$16-($B$9:$B$12),$D$9:$D$12-N(+$D$8:$D$11))

[wk9128]

Cell D16 formula

Please Login or Register  to view this content.

[XLent]

why on earth would you suggest hardwiring the values ?!

[kvsrinivasamurthy]

Pl see file.Column E is used as helper column. you can add any number of rows and can change the value in B16.
In E2 then copied down

=IF((B9-$B$9+1)<$B$16,IF(C9-$B$9<=$B$16,(C9-B9+1),($B$16-(B9-$B$9)))*D9,0)

In D16

=SUM($E$9:$E$12)

[A___2000]

Pl see file.Column E is used as helper column. you can add any number of rows and can change the value in B16.
In E2 then copied down

=IF((B9-$B$9+1)<$B$16,IF(C9-$B$9<=$B$16,(C9-B9+1),($B$16-(B9-$B$9)))*D9,0)

In D16

=SUM($E$9:$E$12)

E is helper column was very useful

i appreciate the help thanks

[A___2000]

thanks everybody, i have now what i need

[kvsrinivasamurthy]

There is simple formula.
With helper column E.
In E2 then copy down

=MAX(MIN($B$16,C9)-B9+1,0)*D9

Without helper column. ARRAY formula

=SUM((IF($C$9:$C$12>$B$16,$B$16,$C$9:$C$12)-IF($B$9:$B$12>$B$16,$B$16,$B$9:$B$12-1))*$D$9:$D$12)

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[JohnTopley]

Yet another

=SUMPRODUCT(($B$16>=$B$9:$B$12)*($B$16+1-$B$9:$B$12),$E$9:$E$12)

See attached