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