If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum a filtered range
Can anybody help me with the calc to sum a filtered range?
|
#2
|
|||
|
|||
Sum a filtered range
Hi Peter
=SUBTOTAL(9,A3:A1000) I usually like to put such values at the top of my column, so assuming there was a header in row 1 and data from row 2 downward, I would Insert a new row at row 1 and put the above formula in cell A1 Select cell A3WindowsFreeze Panes and your header and Subtotal will always be visible. The above formula works with all versions of Excel and totals the values of cells that are still visible after an Autofilter has been applied From XL2003 onward, the facility to also ignore Manually hidden rows was also introduced, you just use 109 in place of 9 =SUBTOTAL(109,A3:A1000) Take a look at Help on Subtotal, and you will see what the other parameters 1 through 8 also provide. -- Regards Roger Govier "Peter" wrote in message news Can anybody help me with the calc to sum a filtered range? |
#3
|
|||
|
|||
Sum a filtered range
Hi Roger,
Thanks for your help... works great - I'm a happy Excel player again! Regards, Peter "Roger Govier" wrote: Hi Peter =SUBTOTAL(9,A3:A1000) I usually like to put such values at the top of my column, so assuming there was a header in row 1 and data from row 2 downward, I would Insert a new row at row 1 and put the above formula in cell A1 Select cell A3WindowsFreeze Panes and your header and Subtotal will always be visible. The above formula works with all versions of Excel and totals the values of cells that are still visible after an Autofilter has been applied From XL2003 onward, the facility to also ignore Manually hidden rows was also introduced, you just use 109 in place of 9 =SUBTOTAL(109,A3:A1000) Take a look at Help on Subtotal, and you will see what the other parameters 1 through 8 also provide. -- Regards Roger Govier "Peter" wrote in message news Can anybody help me with the calc to sum a filtered range? |
Thread Tools | |
Display Modes | |
|
|