Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
Hi, I have a huge spreadsheet with over 250 columns. There are over 40
columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
With the Sales indicator in Row 1 and the numbers in row 2:
=sumproduct(--($1:$1="Sales"),2:2) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html FJ wrote: Hi, I have a huge spreadsheet with over 250 columns. There are over 40 columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
Dave's solution is much simpler and easier to maintain, but just as a side
note, Sum can accept more than 30 cell/range references. For example: =SUM((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A 14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26 ,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A 39),A40) By enclosing the first 39 cell references in ( ), they are treated as one argument. A40 is actually the second argument. "FJ" wrote: Hi, I have a huge spreadsheet with over 250 columns. There are over 40 columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
Hi, Dave, thanks for your response. I tried your suggestion and it worked.
:) Thanks again! "Dave Peterson" wrote: With the Sales indicator in Row 1 and the numbers in row 2: =sumproduct(--($1:$1="Sales"),2:2) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html FJ wrote: Hi, I have a huge spreadsheet with over 250 columns. There are over 40 columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
Hi, JMB, thanks for your response. That's a really great idea to enclose
arguments in parentheses like that to be able to add more. It's a good tip to remember. :) Thanks again! "JMB" wrote: Dave's solution is much simpler and easier to maintain, but just as a side note, Sum can accept more than 30 cell/range references. For example: =SUM((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A 14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26 ,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A 39),A40) By enclosing the first 39 cell references in ( ), they are treated as one argument. A40 is actually the second argument. "FJ" wrote: Hi, I have a huge spreadsheet with over 250 columns. There are over 40 columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
You could just do this with SUMIF (which is less resource intensive
than Sumproduct): =SUMIF($1:$1,"Sales",$2:$2) Best regards Richard FJ wrote: Hi, I have a huge spreadsheet with over 250 columns. There are over 40 columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
Dave -- The Sumproduct() function can not use ENTIRECOLUMN references
like A:A, or H:H < but must (according to your example - which works) can use ENTIREROW references-- 2:2. Hummm.. Can you confirm? " wrote in message : With the Sales indicator in Row 1 and the numbers in row 2: =sumproduct(--($1:$1="Sales"),2:2) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html FJ wrote: Hi, I have a huge spreadsheet with over 250 columns. There are over 40 columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
In earlier versions of excel (xl2003 and below), you can use the entire row, but
not the entire column. In xl2007, you can use the entire column (from what I've read). But in this case, Richard's suggestion is better. JMay wrote: Dave -- The Sumproduct() function can not use ENTIRECOLUMN references like A:A, or H:H < but must (according to your example - which works) can use ENTIREROW references-- 2:2. Hummm.. Can you confirm? " wrote in message : With the Sales indicator in Row 1 and the numbers in row 2: =sumproduct(--($1:$1="Sales"),2:2) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html FJ wrote: Hi, I have a huge spreadsheet with over 250 columns. There are over 40 columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
Always appreciate your help Dave;
Enjoy the holiday season, Jim May " wrote in message : In earlier versions of excel (xl2003 and below), you can use the entire row, but not the entire column. In xl2007, you can use the entire column (from what I've read). But in this case, Richard's suggestion is better. JMay wrote: Dave -- The Sumproduct() function can not use ENTIRECOLUMN references like A:A, or H:H < but must (according to your example - which works) can use ENTIREROW references-- 2:2. Hummm.. Can you confirm? " wrote in message : With the Sales indicator in Row 1 and the numbers in row 2: =sumproduct(--($1:$1="Sales"),2:2) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html FJ wrote: Hi, I have a huge spreadsheet with over 250 columns. There are over 40 columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding noncontiguous cells
I still like Richard's suggestion better in this case <g...
Same to you and yours. JMay wrote: Always appreciate your help Dave; Enjoy the holiday season, Jim May " wrote in message : In earlier versions of excel (xl2003 and below), you can use the entire row, but not the entire column. In xl2007, you can use the entire column (from what I've read). But in this case, Richard's suggestion is better. JMay wrote: Dave -- The Sumproduct() function can not use ENTIRECOLUMN references like A:A, or H:H < but must (according to your example - which works) can use ENTIREROW references-- 2:2. Hummm.. Can you confirm? " wrote in message : With the Sales indicator in Row 1 and the numbers in row 2: =sumproduct(--($1:$1="Sales"),2:2) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html FJ wrote: Hi, I have a huge spreadsheet with over 250 columns. There are over 40 columns with the heading "Sales" and I have to add the numbers under each of the "Sales" headings together. Does anyone know the best way to do this? Clicking on every cell is timeconsuming, but even trying that doesn't work because apparently there is a limit to how much Excel will accept in a SUM formulas of that nature. Is there a way to do this using SUMIF to specify adding only the numbers under the "Sales" headings or any other way? Thanks in advance for any help or suggestions. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding cells witht the same name | Excel Discussion (Misc queries) | |||
Adding up multiple cells | Excel Worksheet Functions | |||
Adding cells with the same color, sub works but function doesnt | Excel Discussion (Misc queries) | |||
adding the same prefix or suffix to a range of cells | Excel Discussion (Misc queries) | |||
Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) |