| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilter & Sumproduct
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Autofilter & Sumproduct
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 "jwwj232" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Autofilter & Sumproduct
Thank you very much.
"Peo Sjoblom" wrote: 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 "jwwj232" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
| Using Sumproduct in Autofilter | Excel Worksheet Functions | |||
| SUMPRODUCT with Autofilter | Excel Worksheet Functions | |||
| Using AutoFilter + sumproduct formula | Excel Discussion (Misc queries) | |||
| SUMPRODUCT and (perhaps) SUBTOTAL with AUTOFILTER | Excel Worksheet Functions | |||
Linear Mode
