Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / New Users / April 2007

Tip: Looking for answers? Try searching our database.

Can SUMPRODUCT be used for entire column?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Hall - 14 Apr 2007 16:25 GMT
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?
Reply to this Message
JE McGimpsey - 14 Apr 2007 16:34 GMT
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"))

> This formula results in a numeric result:
> =SUMPRODUCT((A1:A20)*(B1:B20="b"))
[quoted text clipped - 5 lines]
> time.  Any way to do this for the entire column, without having to specify
> the length of the column?
Reply to this Message
Roger Govier - 14 Apr 2007 18:01 GMT
Just a quick "heads up" JE, I'm sure you meant to restrict column B as
well

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

Signature

Regards

Roger Govier

> XL07 removed the limitation on array formulas (which SUMPRODUCT is,
> even
[quoted text clipped - 25 lines]
>> specify
>> the length of the column?
Reply to this Message
JE McGimpsey - 14 Apr 2007 19:09 GMT
mumble, mumble, fat fingers, mumble, mumble...

Thanks for the correction.

> Just a quick "heads up" JE, I'm sure you meant to restrict column B as
> well
[quoted text clipped - 30 lines]
> >> specify
> >> the length of the column?
Reply to this Message
Stan Brown - 14 Apr 2007 19:13 GMT
Sat, 14 Apr 2007 09:34:57 -0600 from JE McGimpsey
<jemcgimpsey@mvps.org>:
> 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.

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                 http://OakRoadSystems.com/

Reply to this Message
T. Valko - 14 Apr 2007 19:44 GMT
What type of data is in the range? Text? Numbers? Both? Are there any
formula blanks?

Biff

> Sat, 14 Apr 2007 09:34:57 -0600 from JE McGimpsey
> <jemcgimpsey@mvps.org>:
[quoted text clipped - 13 lines]
> 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.
Reply to this Message
Stan Brown - 15 Apr 2007 17:05 GMT
Sat, 14 Apr 2007 14:44:16 -0400 from T. Valko
<biffinpitt@comcast.net>:

> > Sat, 14 Apr 2007 09:34:57 -0600 from JE McGimpsey
> > <jemcgimpsey@mvps.org>:
> >>     http://cpearson.com/excel/named.htm#Dynamic

> >             I have a worksheet where I frequently delete all the
> > elements from one or more non-contiguous rows. (I don't delete the
[quoted text clipped - 4 lines]
> > 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.

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

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                 http://OakRoadSystems.com/

Reply to this Message
Stan Brown - 14 Apr 2007 18:41 GMT
Sat, 14 Apr 2007 11:25:02 -0400 from Hall <hall@garp.org>:
> This formula results in a numeric result:
> =SUMPRODUCT((A1:A20)*(B1:B20="b"))
[quoted text clipped - 5 lines]
> 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?

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                 http://OakRoadSystems.com/

Reply to this Message
Hall - 14 Apr 2007 18:51 GMT
Yes they are.  Good question.

Thanks all.

> Sat, 14 Apr 2007 11:25:02 -0400 from Hall <hall@garp.org>:
>> This formula results in a numeric result:
[quoted text clipped - 12 lines]
> Are columns A and B entirely blank, except for the range you are
> interested in?
Reply to this Message
Stan Brown - 14 Apr 2007 19:10 GMT
Sat, 14 Apr 2007 13:51:50 -0400 from Hall <hall@garp.org>:

> > Sat, 14 Apr 2007 11:25:02 -0400 from Hall <hall@garp.org>:
> >> This formula results in a numeric result:
[quoted text clipped - 6 lines]
> >> 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?

> 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

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                 http://OakRoadSystems.com/

Reply to this Message
Roger Govier - 14 Apr 2007 19:22 GMT
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
Signature

Regards

Roger Govier

> Sat, 14 Apr 2007 13:51:50 -0400 from Hall <hall@garp.org>:
>
[quoted text clipped - 21 lines]
>
> reference: http://www.xldynamic.com/source/xld.SUMPRODUCT.html#format
Reply to this Message

 Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.