Combining SUMIF() and SUBTOTAL() for Dynamic Subtotal
Solution 1:
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))
Solution 2:
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"