SUBTOTAL/SUMIF on autofilter visible cells only

Hi,

First time posting, but I've lurked/searched here for awhile now. I am having a bit of a problem getting a subtotal to work with my autofilter. On my second sheet I currently have my sheet to autofilter by customer name, products, or counties.

I would like my SUMIF statements found below to be automatically adjusted as I autofilter by Customer name.

I have looked at SUBTOTAL functions and even some more advanced (for me) VBA filter modules including special cells (visible cells only) however, I cannot seem to get them to work right.

I have this currently, which works fine for my Sheet1 purposes
=SUMIF(H9:H27,"METHANOL",J9:J27)

I would like my SUBTOTALS to use SUMIF so that I can qualify it with the products in column H. In short, I'd like my SUBTOTAL to act the same way my SUMIF statement works, but with visible cells only.

Hope that makes sense. Thanks in advance.

You can use this formula

=SUMPRODUCT(--(H9:H27="METHANOL"),SUBTOTAL(9,OFFSET(J9:J27,ROW(J9:J27)-MIN(ROW(J9:J27)),0,1)))

Thank you. The formula worked perfectly daddylonglegs.
Martin thank you, that link will help me understand a little more about visible cells,etc.

The only real difference, Martin, is that your version, uses SUBTOTAL(3 for a COUNTA and then multiplies by the range B2:B18. If you use SUBTOTAL(9 for a SUM then you don't need to multiply by B2:B18 so you can use just

=SUMPRODUCT((A2:A18="martin")*SUBTOTAL(9,OFFSET(A2,ROW(A2:A18)-ROW(A2),)))

My version, where the full range J9:J27 is always used, is a little longer but lends itself to a situation where you have named ranges, rather than references

Can someone please tell me how I tell excel to display the value selected from an autofilter list into another cell, be it on the same sheet or another sheet? Any information/advice would be greatly recieved. I have attached a dummy workbook. On sheet1, I would like cell A3, B3, C3 & D3 to display the value selected from their respective list. Thanks Mikal.

This post has been very helpful for summing on an AutoFiltered List.

Is there a way to modify the formula so that I pick up two conditions to satisfy the SUBTOTAL on J9:J27?

For instance, I want to do the same SUBTOTAL on J9:J27 if BOTH H9:H27="METHANOL" and if G9:G27="PLANT A".

I've tried to moditfy the formula but have not had success.

Any suggestions would be greatly appreciated.

Thanks,

Akarupert

Thank you daddylonglegs for figuring that out. I was able to modify it for my spreadsheet and it worked like a charm right out of the gate.