Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding Hidden Rows from calculations
Not sure if this can be done??
I have a worksheet where a number of the rows are hidden and need to exclude these from calculations performed using SUMPRODUCT, ie; =SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1)) but only looking at rows that are not hidden. Is there a relatively simple way of doing this? If not I will just create another column to show an 'X' when the row is not to be counted and then make the calculation: =SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1)*(E1:E2000<"X")) Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding Hidden Rows from calculations
It depends on the Excel version, 2003 and later can ignore hidden as in
formatrowhide, earlier versions can ignore hidden by a filter Also if you are going to use unary minuses it should look like this\ =SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1)) otherwise you might as well remove it and use =SUMPRODUCT((A1:A2000=C1)*(B1:B2000=D1)) Anyway with 2003 and later and hidden rows as opposed to filtered rows us =SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1),SUBTOTAL(103,OFFSET($A$1,ROW($A$1:$A $2000)-MIN(ROW($A$1:$A$1000)),,))) -- Regards, Peo Sjoblom "Terry Bennett" wrote in message ... Not sure if this can be done?? I have a worksheet where a number of the rows are hidden and need to exclude these from calculations performed using SUMPRODUCT, ie; =SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1)) but only looking at rows that are not hidden. Is there a relatively simple way of doing this? If not I will just create another column to show an 'X' when the row is not to be counted and then make the calculation: =SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1)*(E1:E2000<"X")) Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding Hidden Rows from calculations
Wow!
Many thanks Peo. "Peo Sjoblom" wrote in message ... It depends on the Excel version, 2003 and later can ignore hidden as in formatrowhide, earlier versions can ignore hidden by a filter Also if you are going to use unary minuses it should look like this\ =SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1)) otherwise you might as well remove it and use =SUMPRODUCT((A1:A2000=C1)*(B1:B2000=D1)) Anyway with 2003 and later and hidden rows as opposed to filtered rows us =SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1),SUBTOTAL(103,OFFSET($A$1,ROW($A$1:$A $2000)-MIN(ROW($A$1:$A$1000)),,))) -- Regards, Peo Sjoblom "Terry Bennett" wrote in message ... Not sure if this can be done?? I have a worksheet where a number of the rows are hidden and need to exclude these from calculations performed using SUMPRODUCT, ie; =SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1)) but only looking at rows that are not hidden. Is there a relatively simple way of doing this? If not I will just create another column to show an 'X' when the row is not to be counted and then make the calculation: =SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1)*(E1:E2000<"X")) Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding Hidden Rows from calculations
You are welcome
-- Regards, Peo Sjoblom "Terry Bennett" wrote in message ... Wow! Many thanks Peo. "Peo Sjoblom" wrote in message ... It depends on the Excel version, 2003 and later can ignore hidden as in formatrowhide, earlier versions can ignore hidden by a filter Also if you are going to use unary minuses it should look like this\ =SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1)) otherwise you might as well remove it and use =SUMPRODUCT((A1:A2000=C1)*(B1:B2000=D1)) Anyway with 2003 and later and hidden rows as opposed to filtered rows us =SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1),SUBTOTAL(103,OFFSET($A$1,ROW($A$1:$A $2000)-MIN(ROW($A$1:$A$1000)),,))) -- Regards, Peo Sjoblom "Terry Bennett" wrote in message ... Not sure if this can be done?? I have a worksheet where a number of the rows are hidden and need to exclude these from calculations performed using SUMPRODUCT, ie; =SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1)) but only looking at rows that are not hidden. Is there a relatively simple way of doing this? If not I will just create another column to show an 'X' when the row is not to be counted and then make the calculation: =SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1)*(E1:E2000<"X")) Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of a column excluding hidden rows | Excel Worksheet Functions | |||
Counting non-blank cells in a column, excluding hidden rows | Excel Worksheet Functions | |||
excluding #DIV/0! in further calculations | Excel Worksheet Functions | |||
Need to sum columns, excluding hidden ones - like 'subtotal' for r | Excel Discussion (Misc queries) | |||
paste excel sheet excluding hidden rows | Excel Discussion (Misc queries) |