Need a formula for SUM as per given slab criteria

[shaikhrulez]

Hello Experts,

Please refer to attached file.

I need a formula to SUM the given amount as per given slab criteria.

Thanks

 

[Khalid NGO]

Hello Waqar,
I have used your slab criteria in 2 different columns.
and used the following array formula:

=SUM(IF($C$2:$C$78>=G2,IF($C$2:$C$78<=H2,$C$2:$C$78)))
to be entered with CSE

See the attached yellow cells.

Regards,

 

[Chihiro]

SUMPRODUCT or SUMIFS will do it.
I'd also recommend splitting your criteria to 2 columns (i.e. Floor and Ceiling).
Last row will have different formula than rest.

 

[Khalid NGO]

Oops...
After seeing Chihiro's solutions, I realized I missed the rate part..
use the following revised formula:
=SUM(IF($B$2:$B$78>=G2,IF($B$2:$B$78<=H2,$C$2:$C$78)))

again with CSE.

 

[shaikhrulez]

Oops...
After seeing Chihiro's solutions, I realized I missed the rate part..
use the following revised formula:
=SUM(IF($B$2:$B$78>=G2,IF($B$2:$B$78<=H2,$C$2:$C$78)))

again with CSE.

Yeah, I was about to post my question again, as I was looking for the solution you've provided in your 2nd post. Thanks Khalid

Thanks @Chihiro your solution is shorter, I tried doing this with SUMIF but couldn't make it. Though, I wouldn't be able to derive this formula myself.

You guys are need little or no explanation.. !

 

[Chihiro]

Using SUMIF you need additional operation.
First cell would be =SUMIF(B:B,"<="&I2)
There after: =SUMIF(B:B,"<="&I3)-SUM($L$2:L2) copy down.

Basically subtracting sum of values with rate lower than the Floor from calculation sum of values with rate "<=" Ceiling.

See attached.