SUBTOTAL IF formula with auto filter

**PC Pretorius** · 05-21-2013, 10:36 AM

I want to use auto filter in one column and then sum another column based on a certain value in that column and it must change with auto filter.

**Pete_UK** · 05-21-2013, 11:16 AM

Use something like this:

=SUBTOTAL(9,cell_range_in_other_column)

Hope this helps.

Pete

**PC Pretorius** · 05-22-2013, 05:23 AM

Thansk Pete but I want to filter one column and subtotal that column and then use a criteria to sum different things in another column see below. Want to filter direction and then want totals for Cash, Credit cards etc that must change according to the filter. thank you
Year Plaza Direction Date Hour No Class MOP Vehicle Count Row
Cash
Credit Card
Document
e-tag PO
Violation

2012 CAROUSEL MAIN North 27-04-2012 12 1 Cash 2,579 1
2012 CAROUSEL MAIN South 27-04-2012 12 1 Cash 371 1
2012 CAROUSEL MAIN North 27-04-2012 12 1 Credit Card 716 1
2012 CAROUSEL MAIN South 27-04-2012 12 1 Credit Card 173 1
2012 CAROUSEL MAIN North 27-04-2012 12 1 Document 1 1
2012 CAROUSEL MAIN North 27-04-2012 12 1 e-tag PO 38 1
2012 CAROUSEL MAIN South 27-04-2012 12 1 e-tag PO 22 1
2012 CAROUSEL MAIN North 27-04-2012 12 1 Violation 3 1
2012 CAROUSEL MAIN North 27-04-2012 12 2 Cash 43 1
2012 CAROUSEL MAIN South 27-04-2012 12 2 Cash 16 1
2012 CAROUSEL MAIN North 27-04-2012 12 2 Credit Card 5 1
2012 CAROUSEL MAIN South 27-04-2012 12 2 Credit Card 17 1
2012 CAROUSEL MAIN North 27-04-2012 12 2 e-tag PO 1 1
2012 CAROUSEL MAIN South 27-04-2012 12 2 e-tag PO 1 1

**daddylonglegs** · 05-22-2013, 05:45 AM

If amounts to total are in J2:J100 and description like "Cash", "Credit card" in H2:H100 then this formula will give you the total for "Cash" only considering the visible rows

=SUMPRODUCT(SUBTOTAL(9,OFFSET(J2,ROW(J2:J100)-ROW(J2),0))*(H2:H100="Cash"))

....or if you want to keep the formulas simpler use a helper column to indicate which rows are visible, e.g. in Z2 use this formula

=SUBTOTAL(3,H2)

and copy down

Then the formula for summing visible cash amounts would be

=SUMIFS(J:J,Z:Z,1,H:H,"Cash")