Can SUMPRODUCT be used for entire column?

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

 

[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"))

 

[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"))

 

[Stan Brown]

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?

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?

 

[Hall]

Yes they are. Good question.

Thanks all.

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?

 

[JE McGimpsey]

mumble, mumble, fat fingers, mumble, mumble...

Thanks for the correction.

 

[Stan Brown]

Yes they are. Good question.

I believe (untested) that you need to coerce the second argument to
numeric, like this:
=SUMPRODUCT((A:A), --(B:B="b"))

reference: http://www.xldynamic.com/source/xld.SUMPRODUCT.html#format

 

[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

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.

 

[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. Valko]

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

Biff

 

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

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