Autofilter & Sumproduct

G

Guest

I am summing the product of data in columns B and C. Rows 1 thru 4 are header
rows. Data starts on row 5 and goes thru row 500. When filtering column A, I
want to sum the products of only the visible data in columns B and C. I
tried the following formula
=SUM(SUBTOTAL(3,OFFSET(B4,ROW(B5:B500)-ROW(B4),1))*B5:B500*C5:C500).
Returned #VALUE! when inputing ctrl-shift-enter. Help will be greatly
appreciated. Thank you.
 
P

Peo Sjoblom

You need to enter it with ctrl + shift & enter but if you change it to

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B4,ROW(B5:B500)-ROW(B4),1)),B5:B500,C5:C500)


it can be entered normally


--


Regards,


Peo Sjoblom
 
G

Guest

Thank you very much.

Peo Sjoblom said:
You need to enter it with ctrl + shift & enter but if you change it to

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B4,ROW(B5:B500)-ROW(B4),1)),B5:B500,C5:C500)


it can be entered normally


--


Regards,


Peo Sjoblom
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT question, finding data on multiple criteria 1
Weighted Average 7
Best way to insert rows 5
Conditional SUBTOTAL in XL210 Table 7
Getting Text data using the Data - Subtotal feature 1
Excel Complex Summing 3
sum up values at intervals 1
=SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New 3

Top