Simple Sumproduct of 2 Columns, with some blank cells in range

[SteveC]

ColA ColB
5 10
5 10
5
5 10

I'd like to sum the products of the two columns, while ignoring rows that
have a blank cell, so in this case (5*10) + (5*10) + (5*10) --->it's
skipping the third row.

I'm not having luck adjusting a simple =sumproduct(ColA,ColB) formula. I
did a search but didn't come up with anything that I understand how to
adjust.

Thanks for your help!
SteveC

[SteveC]

aha, if you use an array formula ctrl shift enter it works... but is there
another way?

"SteveC" wrote:

> ColA ColB
> 5 10
> 5 10
> 5
> 5 10
>
> I'd like to sum the products of the two columns, while ignoring rows that
> have a blank cell, so in this case (5*10) + (5*10) + (5*10) --->it's
> skipping the third row.
>
> I'm not having luck adjusting a simple =sumproduct(ColA,ColB) formula. I
> did a search but didn't come up with anything that I understand how to
> adjust.
>
> Thanks for your help!
> SteveC
>

[Don Guillett]

try this. Need NOT array enter
=SUMPRODUCT((C2:C5>0)*(D2:D5>0)*C2:D5)

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"SteveC" <SteveC@discussions.microsoft.com> wrote in message
news:AC5F8A23-56C9-4039-ADBE-C33418A05309@microsoft.com...
> ColA ColB
> 5 10
> 5 10
> 5
> 5 10
>
> I'd like to sum the products of the two columns, while ignoring rows that
> have a blank cell, so in this case (5*10) + (5*10) + (5*10) --->it's
> skipping the third row.
>
> I'm not having luck adjusting a simple =sumproduct(ColA,ColB) formula. I
> did a search but didn't come up with anything that I understand how to
> adjust.
>
> Thanks for your help!
> SteveC
>

[Bob Phillips]

=SUMPRODUCT(A1:A4,B1:B4)

will do it.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"SteveC" <SteveC@discussions.microsoft.com> wrote in message
news:3922D344-FF62-4B43-86D0-1C9B54BA462D@microsoft.com...
> aha, if you use an array formula ctrl shift enter it works... but is there
> another way?
>
> "SteveC" wrote:
>
> > ColA ColB
> > 5 10
> > 5 10
> > 5
> > 5 10
> >
> > I'd like to sum the products of the two columns, while ignoring rows
that
> > have a blank cell, so in this case (5*10) + (5*10) + (5*10) --->it's
> > skipping the third row.
> >
> > I'm not having luck adjusting a simple =sumproduct(ColA,ColB) formula.
I
> > did a search but didn't come up with anything that I understand how to
> > adjust.
> >
> > Thanks for your help!
> > SteveC
> >

[SteveC]

Don and Bob, thanks.

"Bob Phillips" wrote:

> =SUMPRODUCT(A1:A4,B1:B4)
>
> will do it.
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "SteveC" <SteveC@discussions.microsoft.com> wrote in message
> news:3922D344-FF62-4B43-86D0-1C9B54BA462D@microsoft.com...
> > aha, if you use an array formula ctrl shift enter it works... but is there
> > another way?
> >
> > "SteveC" wrote:
> >
> > > ColA ColB
> > > 5 10
> > > 5 10
> > > 5
> > > 5 10
> > >
> > > I'd like to sum the products of the two columns, while ignoring rows
> that
> > > have a blank cell, so in this case (5*10) + (5*10) + (5*10) --->it's
> > > skipping the third row.
> > >
> > > I'm not having luck adjusting a simple =sumproduct(ColA,ColB) formula.
> I
> > > did a search but didn't come up with anything that I understand how to
> > > adjust.
> > >
> > > Thanks for your help!
> > > SteveC
> > >
>
>
>