Sum Opening Balance Based on Filtered Criteria

[shaikhrulez]

Hello everyone,

I got another excel query. Please refer to attached file.

I want sum of Amount in cell D1 for Amount preceding the range which is being displayed after filtering.

For example when I filter the range by dates and select month of February only, the Opening Balance (Cell D1) should reflect 333,141 i.e. amount for the month of January only.

&

When I filter the range by dates and select month of March & April only, the Opening Balance (Cell D1) should reflect 622,065 i.e. sum of amount for the month of January & February.

Is there any way to work this out?

Thanks

 

[Dharlome08]

Hi Shaikhrulez,

Replacing your formula with =SUMIF(B5:B34,"<"& SUBTOTAL(5,B5:B34),D5:D34) should work. Example attached.

It should provide your opening balance for whatever date selection you make.

 

[shaikhrulez]

Hi Shaikhrulez,

Replacing your formula with =SUMIF(B5:B34,"<"& SUBTOTAL(5,B5:B34),D5:D34) should work. Example attached.

It should provide your opening balance for whatever date selection you make.

This formula worked..! Thanks. @Dharlome08

However, there is one more condition I want to tackle and that is when I filter the table by Location, the opening balance should not sum up the amount of those locations which I have not filtered.

For example, when I filter the range by location and select location C only and then filter the range again by dates and uncheck first 2 months i.e. January & February, the resultant opening balance should be 181,437 which is sum of 88,376 and 93,061 of 03.01.2016 and 13.02.2016 respectively.

Is it possible to get above results?

 

[rahulshewale1]

Hi Shaikhrulez,

Please see below formula .

=SUMIFS($D$5:$D$34,$B$5:$B$34,"<"&SUBTOTAL(5,$B$5:$B$34),$C$5:$C$34,"C")


Thanks
Rahul shewale

 

[shaikhrulez]

Hi Shaikhrulez,

Please see below formula .

=SUMIFS($D$5:$D$34,$B$5:$B$34,"<"&SUBTOTAL(5,$B$5:$B$34),$C$5:$C$34,"C")


Thanks
Rahul shewale

Thanks Rahul for your input, your solution will only take care of Location C. It will not give desired result for other locations. I mentioned location C just for the sake of example.

 

[rahulshewale1]

hii Hi Shaikhrulez,

i totally misundersood,please find the attached sheet.


=SUMIFS($D$5:$D$34,$B$5:$B$34,"<"&SUBTOTAL(5,$B$5:$B$34),$C$5:$C$34,INDEX($C$5:$C$34,MATCH(RIGHT("<"&SUBTOTAL(5,$B$5:$B$34),5)+0,$B$5:$B$34)))

Thanks
Rahul shewale

 

[shaikhrulez]

hii Hi Shaikhrulez,

i totally misundersood,please find the attached sheet.


=SUMIFS($D$5:$D$34,$B$5:$B$34,"<"&SUBTOTAL(5,$B$5:$B$34),$C$5:$C$34,INDEX($C$5:$C$34,MATCH(RIGHT("<"&SUBTOTAL(5,$B$5:$B$34),5)+0,$B$5:$B$34)))

Thanks
Rahul shewale

Wow, thanks Rahul this one worked perfectly. I really like the logic behind it, it is easier to understand.

In the meanwhile, I figure out another solution, here it is.

=SUMIFS(D5:D34,B5:B34,"<"&SUBTOTAL(5,B5:B34),C5:C34,"="&INDIRECT("C"&MIN(IF(SUBTOTAL(3,OFFSET(C5,ROW(C5:C34)-ROW(C5),,1)),ROW(C5:C34)))))

I copied this part INDIRECT("C"&MIN(IF(SUBTOTAL(3,OFFSET(C5,ROW(C5:C34)-ROW(C5),,1)),ROW(C5:C34) from another website for picking up value of first filtered cell.

However, your formula is amazing. It is much faster, my formula could slow down Excel due to usage of INDIRECT / OFFSET functions.

Thanks buddy