Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with unhidden data
Hi, all
I have a simple question about how to do sumproduct with the unhidden data within the rage. The function I want is pretty like subtotal(109,..) fuction, will only do the calculation based on the unhidden data after I apply the auto filter. For example, A B C D 100 2 200 4 300 3 400 1 500 5 500 5 600 2 600 2 700 5 700 5 800 4 800 4 900 5 900 5 1000 9 1000 9 26200 23900 <= sumproduct Column A and B is original range, so 26200=sumproduct(A1:A10,B1:B10) Column C and D is just represent when I use a auto fileter for column A = 500, and I want to capture the new data's weighted average in the same cell, but don't know which function or how I should adjust the sumproduct to do that. But the goal is 23900=sumproduct(A1:A10,B1:B10) but only calculate the result data based on how I filter it. Does anyone how to do this? Maybe I need to add some reference flag to do that? Thank you Vincent |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with unhidden data
assuming your data is in A1:B11 (with a header in row 1), try:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT("1:" &ROWS(A2:A11))),0)),B2:B11) with XL03 or later, I believe you can use 109 option for subtotal to include rows hidden by the user. " wrote: Hi, all I have a simple question about how to do sumproduct with the unhidden data within the rage. The function I want is pretty like subtotal(109,..) fuction, will only do the calculation based on the unhidden data after I apply the auto filter. For example, A B C D 100 2 200 4 300 3 400 1 500 5 500 5 600 2 600 2 700 5 700 5 800 4 800 4 900 5 900 5 1000 9 1000 9 26200 23900 <= sumproduct Column A and B is original range, so 26200=sumproduct(A1:A10,B1:B10) Column C and D is just represent when I use a auto fileter for column A = 500, and I want to capture the new data's weighted average in the same cell, but don't know which function or how I should adjust the sumproduct to do that. But the goal is 23900=sumproduct(A1:A10,B1:B10) but only calculate the result data based on how I filter it. Does anyone how to do this? Maybe I need to add some reference flag to do that? Thank you Vincent |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unhidden row only | Excel Discussion (Misc queries) | |||
unhidden row | New Users to Excel | |||
How do I copy & paste only the unhidden data on a worksheet ? | Excel Discussion (Misc queries) | |||
Calculations on Only Unhidden Data Cells | Excel Discussion (Misc queries) | |||
Is it possible to send a worksheet without the unhidden data? | Excel Worksheet Functions |