Adding noncontiguous cells

F

FJ

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.
 
J

JMB

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,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A39),A40)

By enclosing the first 39 cell references in ( ), they are treated as one
argument. A40 is actually the second argument.
 
F

FJ

Hi, Dave, thanks for your response. I tried your suggestion and it worked.
:) Thanks again!
 
F

FJ

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!
 
R

RichardSchollar

You could just do this with SUMIF (which is less resource intensive
than Sumproduct):

=SUMIF($1:$1,"Sales",$2:$2)

Best regards

Richard
 
J

JMay

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?
 
D

Dave Peterson

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.
 
Top