Combining SUMIF() and SUBTOTAL() for Dynamic Subtotal

I need help combining two formulas.

What I need is to subtotal a range based on a criteria and I need that subtotal to change when the range is filtered.

I have two formulas that work separately. Any help in combining them would be greatly appreciated (I have looked at other posts for hours and cannot work it out!)

 =SUBTOTAL(9,AW5:AW552) =SUMIF(AV$5:AW$552,AV558,AW$5:AW$552)

Thanks very much!

2

3 Answers

I didn't put in the absolute references, but this should work. If it's not perfect, I don't understand your sumif going across columns is all.

=SUMPRODUCT(SUBTOTAL(109,OFFSET(AW5,ROW(AW5:AW552)-ROW(AW5),,1)),--(AV5:AV552=AV558))

1

You can can use subtotal like this

total =subtotal(109,B5:B10) Multiple total formulas can be used

grandtotal =sumifs(B:B,A:A,"total") which only pulls rows labeled "total"

You can filter based on multiple columns, so filter based on 'AV' column values and calculated SUBTOTAL. Of course this is a manual method.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy