Autofilter & Sumproduct

J

jwwj232

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
 
J

jwwj232

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

Getting Text data using the Data - Subtotal feature 1
SUMPRODUCT question, finding data on multiple criteria 1
Help with Sumproduct searching cell contents 2
Conditional Formatting Across Multiple Cells 0
Weighted Average 7
=SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New 3
What is the "--" (double dash) in MS Excel formula? 2
CTRL SHIFT ENTER 2

Top