Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum only visible cells within a range.
My user has a spreadsheet with some columns of dollar values.
What he wants to do is to set up an Auto Filter on the data with a Sum cell at the bottom of the column (or even elsewhere on the page) such that the sum will adjust to show only the sum of those values that are visible in the originally selected range, which includes all of the cells in that column, for the data set. I'm not an Excel programmer so I don't know the VBA object model. I have read all of the newsgroup posts back to 05/15/05 that have the word 'sum' in the subject and I've seen a lot of things that look like they might be the answer, but I can't seem to crack this nut. I know this CAN'T be hard and I actually expect that there's already an Excel function to do this, but I just can't find it. My very closest attempt is this one line function shown below, which doesn't seem to work. Public Function SumVisibleRows(ByVal TheRange As Range) As Currency SumVisibleRows = CCur(Application.Sum(TheRange.SpecialCells(xlCellT ypeVisible))) End Function Thanks for any help! -Jason Kendall |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum only visible cells within a range.
Jason,
Look at help on the SUBTOTAL function. HTH, Bernie MS Excel MVP "Jason Kendall" wrote in message ... My user has a spreadsheet with some columns of dollar values. What he wants to do is to set up an Auto Filter on the data with a Sum cell at the bottom of the column (or even elsewhere on the page) such that the sum will adjust to show only the sum of those values that are visible in the originally selected range, which includes all of the cells in that column, for the data set. I'm not an Excel programmer so I don't know the VBA object model. I have read all of the newsgroup posts back to 05/15/05 that have the word 'sum' in the subject and I've seen a lot of things that look like they might be the answer, but I can't seem to crack this nut. I know this CAN'T be hard and I actually expect that there's already an Excel function to do this, but I just can't find it. My very closest attempt is this one line function shown below, which doesn't seem to work. Public Function SumVisibleRows(ByVal TheRange As Range) As Currency SumVisibleRows = CCur(Application.Sum(TheRange.SpecialCells(xlCellT ypeVisible))) End Function Thanks for any help! -Jason Kendall |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum only visible cells within a range.
I'm believe the SUBTOTAL worksheet function will meet your needs. At the
bottom of the column that has the autofilter enter the formula =SUBTOTAL(9,A1:A10). This is assuming your list is in cells A1-A10. Also, ensure that you place this formula in a cell that is NOT in A1-A10 AND not included in the autofilter list. The result should be the sum of whatever is currently visible in the autofilter list. "Jason Kendall" wrote: My user has a spreadsheet with some columns of dollar values. What he wants to do is to set up an Auto Filter on the data with a Sum cell at the bottom of the column (or even elsewhere on the page) such that the sum will adjust to show only the sum of those values that are visible in the originally selected range, which includes all of the cells in that column, for the data set. I'm not an Excel programmer so I don't know the VBA object model. I have read all of the newsgroup posts back to 05/15/05 that have the word 'sum' in the subject and I've seen a lot of things that look like they might be the answer, but I can't seem to crack this nut. I know this CAN'T be hard and I actually expect that there's already an Excel function to do this, but I just can't find it. My very closest attempt is this one line function shown below, which doesn't seem to work. Public Function SumVisibleRows(ByVal TheRange As Range) As Currency SumVisibleRows = CCur(Application.Sum(TheRange.SpecialCells(xlCellT ypeVisible))) End Function Thanks for any help! -Jason Kendall |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum only visible cells within a range.
the help search engine really sucks as far as i'm concerned. i can enter the
name of a function and click search, it's the last item in the list. that's ridiculous. -- Gary "Jason Kendall" wrote in message ... Bernie, Thanks so much to both you and William Horton for such a speedy and ideal reply. I knew there just HAD to be something to do this, I just couldn't type in the right series of keywords to make the 'SubTotal' function show up. Cheers! -- -Jason Kendall On Thu, 14 Jul 2005 10:42:05 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Jason, Look at help on the SUBTOTAL function. HTH, Bernie MS Excel MVP "Jason Kendall" wrote in message . .. My user has a spreadsheet with some columns of dollar values. What he wants to do is to set up an Auto Filter on the data with a Sum cell at the bottom of the column (or even elsewhere on the page) such that the sum will adjust to show only the sum of those values that are visible in the originally selected range, which includes all of the cells in that column, for the data set. I'm not an Excel programmer so I don't know the VBA object model. I have read all of the newsgroup posts back to 05/15/05 that have the word 'sum' in the subject and I've seen a lot of things that look like they might be the answer, but I can't seem to crack this nut. I know this CAN'T be hard and I actually expect that there's already an Excel function to do this, but I just can't find it. My very closest attempt is this one line function shown below, which doesn't seem to work. Public Function SumVisibleRows(ByVal TheRange As Range) As Currency SumVisibleRows = CCur(Application.Sum(TheRange.SpecialCells(xlCellT ypeVisible))) End Function Thanks for any help! -Jason Kendall |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Performing a function on visible cells only in a range | Excel Discussion (Misc queries) | |||
How do I include only visible cells in a range? | Excel Worksheet Functions | |||
average of visible cells in a filtered range | Excel Worksheet Functions | |||
Select only visible cells in a selected range | Excel Programming | |||
Trying to select the visible range of cells | Excel Programming |