Excel: Have a cell value equal a filter selection

[RandDUser]

Column A = employees last name
Column B = a dollar amount

The range A1:B11 is populated by employees and dollar amounts like so -

Employee $$$
-------- -----
Smith 100
Johnson 200
Allen 2500
Smith 100
Smith 1000
Johnson 950
Allen 700
Allen 8000
Smith 100
Johnson 600
TOTAL =sum(B2:B11)

There are autofilters on both columns. However, when I filter by last name I want the TOTAL to equal my filter choice.

I was thinking of =SUMIF(A2:A11, C1, B2:B11) where C1 = the autofilter value chosen, but I'm not sure how to capture that value. I know of the =CELL() formula, but I dont think that would apply. Any help would be great. Thank you!

 

[GlennUK]

Use the SUBTOTAL function, which only uses the filter results, like:
=SUBTOTAL(9,B2:B11)


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[RandDUser]

Sometimes it's too simple...but that's a good thing. Thank you Glenn.