2

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
  • I don't understand, the sumif does what the subtotal does, but how you want it. Or maybe just use a sumproduct? Jan 31, 2014 at 17:38
  • The SUMIF() sums a range based on a criteria, while the SUBTOTAL() sums the results of the SUMIF(). The idea is that once you filter the rows, the SUBTOTAL() will sum only rows that are still showing. Jan 31, 2014 at 17:56

3 Answers 3

4

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
  • A comment why this works or what this solution consits of would be appreciated.
    – andig
    Mar 19, 2020 at 10:16
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"

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .