Rast8
Reputation: 21
SUMIF and SUBTOAL EXCEL
Is there anyway in Excel to combine SUBTOTAL and SUMIF
- I have one column with a description in
- I have one column with dates in
- I have a final column which contains the calculation.
I want to subtotal (when the date filter is applied) all values that reference the column range which contain plain text words such as "Trading"
My SUMIF works fine on its own it's just I want to SUBTOTAL when a particular date range or any filter on the table is selected.
Appreciate any help on this one
=SUBTOTAL(9,(SUMIF(E4:E200,"Trading",S4:S200)))
Upvotes: 1
Views: 547
Answers (1)
Domenic
Reputation: 8114
You can use a combination of SUMPRODUCT, SUBTOTAL, and OFFSET...
=SUMPRODUCT(--(E4:E200="Trading"),SUBTOTAL(9,OFFSET(S4:S200,ROW(S4:S200)-ROW(S4),0,1)))
Upvotes: 1