| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum many unconnected cells
Would anyone know of an easy way to sum a large quantity of unconnected cells
within a worksheet? The worksheet is on one sheet. We have it broken down into many sections within the spreadsheet and each section has a cell that calculates salestax. I want to sum all of the cells that have salestax into one cell on another worksheet. Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum many unconnected cells
Is there any indications in adjacent cells that these are sales tax or are
they located in a certain pattern like every 5th cell or so? If not then there is no other way than to type =SUM( then select the cells manually by holding down the ctrl key and select each cell one by one -- Regards, Peo Sjoblom "Pam M" wrote in message ... Would anyone know of an easy way to sum a large quantity of unconnected cells within a worksheet? The worksheet is on one sheet. We have it broken down into many sections within the spreadsheet and each section has a cell that calculates salestax. I want to sum all of the cells that have salestax into one cell on another worksheet. Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum many unconnected cells
They are not in a pattern, however the cell to the immediate left is labeled
SalesTax. There are so many, manual selection would be nearly impossible. "Peo Sjoblom" wrote: Is there any indications in adjacent cells that these are sales tax or are they located in a certain pattern like every 5th cell or so? If not then there is no other way than to type =SUM( then select the cells manually by holding down the ctrl key and select each cell one by one -- Regards, Peo Sjoblom "Pam M" wrote in message ... Would anyone know of an easy way to sum a large quantity of unconnected cells within a worksheet? The worksheet is on one sheet. We have it broken down into many sections within the spreadsheet and each section has a cell that calculates salestax. I want to sum all of the cells that have salestax into one cell on another worksheet. Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum many unconnected cells
Pam
If the cell immediately to the left of the cells you want to total is always "SALES TAX" and you want to add up all the number to the right of every instance of "SALES TAX", that would be a pattern; and, the following array formula should work: =SUMPRODUCT(--(OFFSET(B1:E12,0,-1)="SALES TAX")*IF(ISNUMBER(B1:E12), (B1:E12),0)) enter this as an array function (shift-control-enter) with the B1:E12 replaced by the appropriate range. Your range can't include column A, since you can't offset that one column to the left. Of course you can't have any numbers in column A that are required in your total, since they can't have "SALES TAX" in the column to the left. So, that should not be a problem. I really think this should work, but, I I really think it should not replace a redesign of your spreadsheet so that you don't have to resort this kind of formula to get your totals. Good luck Ken Norfolk, Va On Jul 13, 1:44 pm, Pam M wrote: They are not in a pattern, however the cell to the immediate left is labeled SalesTax. There are so many, manual selection would be nearly impossible. "Peo Sjoblom" wrote: Is there any indications in adjacent cells that these are sales tax or are they located in a certain pattern like every 5th cell or so? If not then there is no other way than to type =SUM( then select the cells manually by holding down the ctrl key and select each cell one by one -- Regards, Peo Sjoblom "Pam M" wrote in message ... Would anyone know of an easy way to sum a large quantity of unconnected cells within a worksheet? The worksheet is on one sheet. We have it broken down into many sections within the spreadsheet and each section has a cell that calculates salestax. I want to sum all of the cells that have salestax into one cell on another worksheet. Thanks!- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum many unconnected cells
Hi again Krcowen,
calculates salestax. I want to sum all of the cells that have salestax into one cell on another worksheet. Thanks! how can we modify the formula in the onesheet where salestax comes on *another worksheet*? =SUMPRODUCT(--(OFFSET(B1:E12,0,-1)="SALES TAX")*IF(ISNUMBER(B1:E12), (B1:E12),0)) maybe we need the filename of the *another worksheet*? I guess..<<--- regards, driller -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull " wrote: Pam If the cell immediately to the left of the cells you want to total is always "SALES TAX" and you want to add up all the number to the right of every instance of "SALES TAX", that would be a pattern; and, the following array formula should work: =SUMPRODUCT(--(OFFSET(B1:E12,0,-1)="SALES TAX")*IF(ISNUMBER(B1:E12), (B1:E12),0)) enter this as an array function (shift-control-enter) with the B1:E12 replaced by the appropriate range. Your range can't include column A, since you can't offset that one column to the left. Of course you can't have any numbers in column A that are required in your total, since they can't have "SALES TAX" in the column to the left. So, that should not be a problem. I really think this should work, but, I I really think it should not replace a redesign of your spreadsheet so that you don't have to resort this kind of formula to get your totals. Good luck Ken Norfolk, Va On Jul 13, 1:44 pm, Pam M wrote: They are not in a pattern, however the cell to the immediate left is labeled SalesTax. There are so many, manual selection would be nearly impossible. "Peo Sjoblom" wrote: Is there any indications in adjacent cells that these are sales tax or are they located in a certain pattern like every 5th cell or so? If not then there is no other way than to type =SUM( then select the cells manually by holding down the ctrl key and select each cell one by one -- Regards, Peo Sjoblom "Pam M" wrote in message ... Would anyone know of an easy way to sum a large quantity of unconnected cells within a worksheet? The worksheet is on one sheet. We have it broken down into many sections within the spreadsheet and each section has a cell that calculates salestax. I want to sum all of the cells that have salestax into one cell on another worksheet. Thanks!- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum many unconnected cells
=SUMPRODUCT(--(OFFSET(Sheet2!B1:E12,0,-1)="SALES
TAX")*IF(ISNUMBER(Sheet2!B1:E12),(Sheet2!B1:E12),0 )) "driller" wrote: Hi again Krcowen, calculates salestax. I want to sum all of the cells that have salestax into one cell on another worksheet. Thanks! how can we modify the formula in the onesheet where salestax comes on *another worksheet*? =SUMPRODUCT(--(OFFSET(B1:E12,0,-1)="SALES TAX")*IF(ISNUMBER(B1:E12), (B1:E12),0)) maybe we need the filename of the *another worksheet*? I guess..<<--- regards, driller -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull " wrote: Pam If the cell immediately to the left of the cells you want to total is always "SALES TAX" and you want to add up all the number to the right of every instance of "SALES TAX", that would be a pattern; and, the following array formula should work: =SUMPRODUCT(--(OFFSET(B1:E12,0,-1)="SALES TAX")*IF(ISNUMBER(B1:E12), (B1:E12),0)) enter this as an array function (shift-control-enter) with the B1:E12 replaced by the appropriate range. Your range can't include column A, since you can't offset that one column to the left. Of course you can't have any numbers in column A that are required in your total, since they can't have "SALES TAX" in the column to the left. So, that should not be a problem. I really think this should work, but, I I really think it should not replace a redesign of your spreadsheet so that you don't have to resort this kind of formula to get your totals. Good luck Ken Norfolk, Va On Jul 13, 1:44 pm, Pam M wrote: They are not in a pattern, however the cell to the immediate left is labeled SalesTax. There are so many, manual selection would be nearly impossible. "Peo Sjoblom" wrote: Is there any indications in adjacent cells that these are sales tax or are they located in a certain pattern like every 5th cell or so? If not then there is no other way than to type =SUM( then select the cells manually by holding down the ctrl key and select each cell one by one -- Regards, Peo Sjoblom "Pam M" wrote in message ... Would anyone know of an easy way to sum a large quantity of unconnected cells within a worksheet? The worksheet is on one sheet. We have it broken down into many sections within the spreadsheet and each section has a cell that calculates salestax. I want to sum all of the cells that have salestax into one cell on another worksheet. Thanks!- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
| Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
| paste locked cells and unlocked cells in protected sheet | Excel Worksheet Functions | |||
| trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
| Pasting single cells from Word to multiple cells in Excel | Excel Worksheet Functions | |||
Linear Mode
