Sum an autofiltered column based on a row

[swholmes]

Greetings all,

I'm stumped trying to do something that I thought would be easy (and hopefully is).

I am using Excel 2002

I have a transaction spreadsheet that list details of a sale:

Name Month Sale
John 8 $25
Adam 8 $30
John 8 $30
John 9 $30

I use an auto filter to select which Month I want and a another worksheet that sums the total sales based on the filter. Now I'd like to show the total sales for each person based on that filter.

Using the example above the output I'm looking for when I filter on Month 8 would be:
Total Sales: $85 (which I can do with subtotal)
John's Sales: $55
Adam's Sales: $30

Any suggestions would be greatly appreciated.

Many thanks,
Scott

 

[Aladin Akyurek]

Greetings all,

I'm stumped trying to do something that I thought would be easy (and hopefully is).

I am using Excel 2002

I have a transaction spreadsheet that list details of a sale:

Name Month Sale
John 8 $25
Adam 8 $30
John 8 $30
John 9 $30

I use an auto filter to select which Month I want and a another worksheet that sums the total sales based on the filter. Now I'd like to show the total sales for each person based on that filter.

Using the example above the output I'm looking for when I filter on Month 8 would be:
Total Sales: $85 (which I can do with subtotal)
John's Sales: $55
Adam's Sales: $30

Any suggestions would be greatly appreciated.

Many thanks,
Scott

If you insist having AutoFilter...

A2: John
A3: Adam

B2, copy down:

=SUMPRODUCT(--($A$5:$A$100=$A2),
    SUBTOTAL(9,OFFSET($C$5,ROW($C$5:$C$100)-ROW($C$5),,1)))

 

[swholmes]

Thank you very much for your help Aladin, it worked!

Would it be possible to sum up all of John's sales regardless of the auto-filter?


Thank you for your time,
Scott

 

[Aladin Akyurek]

Thank you very much for your help Aladin, it worked!

You are welcome.

Would it be possible to sum up all of John's sales regardless of the auto-filter?


Thank you for your time,
Scott

If we disregard the month condition and AutoFilter...

C2:

=SUMIF($A$5:$A$100,$A2,$C$5:$C$100)

Is this what you meant to have?

 

[swholmes]

Thanks!