Can SUMPRODUCT be used for entire column?

H

Hall

This formula results in a numeric result:
=SUMPRODUCT((A1:A20)*(B1:B20="b"))

but this formula results in a #NUM! result:
=SUMPRODUCT((A:A)*(B:B="b"))

Which means I need to specify the length of the columns, which may grow over
time. Any way to do this for the entire column, without having to specify
the length of the column?
 
J

JE McGimpsey

XL07 removed the limitation on array formulas (which SUMPRODUCT is, even
though it doesn't require CTRL-SHIFT-ENTER) and entire columns.

For pre-XL07, one can use

=SUMPRODUCT(A1:A65535, --(B1:B65536="b"))

to get all but one cell in the column.

Or one could define the ranges dynamically:

http://cpearson.com/excel/named.htm#Dynamic

and use

=SUMPRODUCT(a_range, --(b_range="b"))
 
R

Roger Govier

Just a quick "heads up" JE, I'm sure you meant to restrict column B as
well

=SUMPRODUCT(A1:A65535, --(B1:B65535="b"))
 
S

Stan Brown

Sat said:
This formula results in a numeric result:
=SUMPRODUCT((A1:A20)*(B1:B20="b"))

but this formula results in a #NUM! result:
=SUMPRODUCT((A:A)*(B:B="b"))

Which means I need to specify the length of the columns, which may grow over
time. Any way to do this for the entire column, without having to specify
the length of the column?
Click to expand...

You got some good answers, but I didn't see anyone ask this question:
Are columns A and B entirely blank, except for the range you are
interested in?
 
H

Hall

Yes they are. Good question.

Thanks all.

Stan Brown said:
You got some good answers, but I didn't see anyone ask this question:
Are columns A and B entirely blank, except for the range you are
interested in?
Click to expand...
 
S

Stan Brown

Sat, 14 Apr 2007 09:34:57 -0600 from JE McGimpsey
Or one could define the ranges dynamically:

http://cpearson.com/excel/named.htm#Dynamic
Click to expand...

The dynamic range is pretty cool, but I wonder if there's any way to
do it when the interior of the range contains blank rows.

Background: I have a worksheet where I frequently delete all the
elements from one or more non-contiguous rows. (I don't delete the
rows, because I want to keep the formatting.) Then I sort the
rectangle, which pushes the blank rows to the bottom.

I'd like to do that with a dynamic range, but I can't figure out a
formula that would say "last nonblank row". It's easy enough to do
in VBA, but if I can do it without a macro I'd prefer that.
 
R

Roger Govier

Hi Stan

As JE has pointed out, unless you have Xl2007 you cannot use whole
columns.

You were quite right to ask the OP whether the rest of the columns was
blank.
He would be better using Dynamic Named ranges to limit the sumproduct
formula to just the range of cell containing Data.

Insert>Name>Define>
Name RngA Refers to =OFFSET($A$1,0,0,counta($A:$A))
Name Rngb Refers to =OFFSET($B|$1,0,0,counta($B:$B))

=SUMPRODUCT(rngA*(RngB="b"))

For more help on Dynamic ranges take a look at
http://www.contextures.com/xlNames01.html#Dynamic

then use
 
T

T. Valko

What type of data is in the range? Text? Numbers? Both? Are there any
formula blanks?

Biff
 
S

Stan Brown

Sat, 14 Apr 2007 14:44:16 -0400 from T. Valko
What type of data is in the range? Text? Numbers? Both? Are there any
formula blanks?
Click to expand...

Thanks for responding. It's all fixed data, no formulas.

Column A contains either a letter S or blank.
Column B contains a date.
Columns C and D contain start and end times.
Column E contains a channel ID (text).
Column F contains a one-letter code.
Column G contains the program title.

Columns B, C, D, E, and G always contain data unless the whole row is
blank (which happens after a program has been recorded and then
erased).
 
Top