Understanding SUMPRODUCT()
 

I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

ISEVEN(A1:A20) cannot generate an array, so it cannot be used with a range
However =SUMPRODUCT(--(MOD(A1:A20,2)=0)) will work
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Jakobshavn Isbrae" wrote in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

Several ways but using sumproduct try this

=SUMPRODUCT(--(MOD(A1:A20,2)=0))

and for odd numbers
=SUMPRODUCT(--(MOD(A1:A20,2)=1))

Mike

"Jakobshavn Isbrae" wrote:

I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

On Sun, 3 Feb 2008 05:13:01 -0800, Jakobshavn Isbrae
wrote:

I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.

I believe the problem is that ISEVEN will only work on a single cell, and
cannot return an array.

To test for even numbers, you could use:

=SUMPRODUCT(ISNUMBER(A1:A20)*(MOD(A1:A20,2)=0))

or, if there will be no blanks at all:

=SUMPRODUCT(--(MOD(A1:A20,2)=0))
--ron

Understanding SUMPRODUCT()
 

Thank you !
--
jake


"Mike H" wrote:

Several ways but using sumproduct try this

=SUMPRODUCT(--(MOD(A1:A20,2)=0))

and for odd numbers
=SUMPRODUCT(--(MOD(A1:A20,2)=1))

Mike

"Jakobshavn Isbrae" wrote:

I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

Don't thank me yet because I've changed my mind, the previous formula
interprets a blank cell as even so use this instead

=SUMPRODUCT(--(A1:A20<""),--(MOD(A1:A20,2)=0))

Mike

"Jakobshavn Isbrae" wrote:

Thank you !
--
jake


"Mike H" wrote:

Several ways but using sumproduct try this

=SUMPRODUCT(--(MOD(A1:A20,2)=0))

and for odd numbers
=SUMPRODUCT(--(MOD(A1:A20,2)=1))

Mike

"Jakobshavn Isbrae" wrote:

I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

Thanks!

One more question...you mentioned that ISEVEN can not generate an array.
Are ISEVEN() and MOD() really that different?

Can any logical function return an array?
--
jake


"Bernard Liengme" wrote:

ISEVEN(A1:A20) cannot generate an array, so it cannot be used with a range
However =SUMPRODUCT(--(MOD(A1:A20,2)=0)) will work
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Jakobshavn Isbrae" wrote in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

Thank you for your very rapid reply. Is there a list somewhere of worksheet
functions that can and cannot return arrays ??

I looked in Excel Help for ISEVEN and there was no mention of this limitation.
--
jake


"Ron Rosenfeld" wrote:

On Sun, 3 Feb 2008 05:13:01 -0800, Jakobshavn Isbrae
wrote:

I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.

I believe the problem is that ISEVEN will only work on a single cell, and
cannot return an array.

To test for even numbers, you could use:

=SUMPRODUCT(ISNUMBER(A1:A20)*(MOD(A1:A20,2)=0))

or, if there will be no blanks at all:

=SUMPRODUCT(--(MOD(A1:A20,2)=0))
--ron

Understanding SUMPRODUCT()
 

Clearly not, as ISEVEN doesn't.

There seems to be no logic as to which functions will and which will not
handle an array, for instance WEEKDAY does, WEEKNUM doesn't. My personal
view FWIIW is that it is all down to the developer that crafted the
particular function.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jakobshavn Isbrae" wrote in
message ...
Thanks!

One more question...you mentioned that ISEVEN can not generate an array.
Are ISEVEN() and MOD() really that different?

Can any logical function return an array?
--
jake


"Bernard Liengme" wrote:

ISEVEN(A1:A20) cannot generate an array, so it cannot be used with a
range
However =SUMPRODUCT(--(MOD(A1:A20,2)=0)) will work
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Jakobshavn Isbrae" wrote in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

Thank you Bob. Do you know of any reference, any book, any website, that
covers this topic?

I guess I can use trial & error, but I hate to reinvent the wheel if there
is a good published source.
--
jake


"Bob Phillips" wrote:

Clearly not, as ISEVEN doesn't.

There seems to be no logic as to which functions will and which will not
handle an array, for instance WEEKDAY does, WEEKNUM doesn't. My personal
view FWIIW is that it is all down to the developer that crafted the
particular function.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jakobshavn Isbrae" wrote in
message ...
Thanks!

One more question...you mentioned that ISEVEN can not generate an array.
Are ISEVEN() and MOD() really that different?

Can any logical function return an array?
--
jake


"Bernard Liengme" wrote:

ISEVEN(A1:A20) cannot generate an array, so it cannot be used with a
range
However =SUMPRODUCT(--(MOD(A1:A20,2)=0)) will work
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Jakobshavn Isbrae" wrote in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

On Sun, 3 Feb 2008 05:45:01 -0800, Jakobshavn Isbrae
wrote:

Thank you for your very rapid reply. Is there a list somewhere of worksheet
functions that can and cannot return arrays ??

I looked in Excel Help for ISEVEN and there was no mention of this limitation.
--
jake

I'm not aware of any such list. But it has been my experience that many of the
functions in the Analysis ToolPak (pre Excel 2007) will not return arrays.

You can check this by using Tools/Formula Auditing/Evaluate formula and
observing when, with a multi-cell argument, an array is returned. Or you can
select the relevant part of the function in the function bar, and hit <f9 to
see the intermediate result.
--ron

Understanding SUMPRODUCT()
 

I am afraid I don't. http://www.xldynamic.com/source/xld.SUMPRODUCT.html has
the most comprehensive coverage of SUMPRODUCT, but I know for a fact that it
doesn't cover that topic. As I said, I know of no logic in which work, which
don't, so I think it is just trial and error. Maybe I will do something and
publish it as well..

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jakobshavn Isbrae" wrote in
message ...
Thank you Bob. Do you know of any reference, any book, any website, that
covers this topic?

I guess I can use trial & error, but I hate to reinvent the wheel if there
is a good published source.
--
jake


"Bob Phillips" wrote:

Clearly not, as ISEVEN doesn't.

There seems to be no logic as to which functions will and which will not
handle an array, for instance WEEKDAY does, WEEKNUM doesn't. My personal
view FWIIW is that it is all down to the developer that crafted the
particular function.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Jakobshavn Isbrae" wrote in
message ...
Thanks!

One more question...you mentioned that ISEVEN can not generate an
array.
Are ISEVEN() and MOD() really that different?

Can any logical function return an array?
--
jake


"Bernard Liengme" wrote:

ISEVEN(A1:A20) cannot generate an array, so it cannot be used with a
range
However =SUMPRODUCT(--(MOD(A1:A20,2)=0)) will work
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Jakobshavn Isbrae" wrote
in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number
of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

Thanks Bob.
--
jake


"Bob Phillips" wrote:

I am afraid I don't. http://www.xldynamic.com/source/xld.SUMPRODUCT.html has
the most comprehensive coverage of SUMPRODUCT, but I know for a fact that it
doesn't cover that topic. As I said, I know of no logic in which work, which
don't, so I think it is just trial and error. Maybe I will do something and
publish it as well..

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jakobshavn Isbrae" wrote in
message ...
Thank you Bob. Do you know of any reference, any book, any website, that
covers this topic?

I guess I can use trial & error, but I hate to reinvent the wheel if there
is a good published source.
--
jake


"Bob Phillips" wrote:

Clearly not, as ISEVEN doesn't.

There seems to be no logic as to which functions will and which will not
handle an array, for instance WEEKDAY does, WEEKNUM doesn't. My personal
view FWIIW is that it is all down to the developer that crafted the
particular function.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Jakobshavn Isbrae" wrote in
message ...
Thanks!

One more question...you mentioned that ISEVEN can not generate an
array.
Are ISEVEN() and MOD() really that different?

Can any logical function return an array?
--
jake


"Bernard Liengme" wrote:

ISEVEN(A1:A20) cannot generate an array, so it cannot be used with a
range
However =SUMPRODUCT(--(MOD(A1:A20,2)=0)) will work
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Jakobshavn Isbrae" wrote
in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number
of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

Thanks Ron
--
jake


"Ron Rosenfeld" wrote:

On Sun, 3 Feb 2008 05:45:01 -0800, Jakobshavn Isbrae
wrote:

Thank you for your very rapid reply. Is there a list somewhere of worksheet
functions that can and cannot return arrays ??

I looked in Excel Help for ISEVEN and there was no mention of this limitation.
--
jake

I'm not aware of any such list. But it has been my experience that many of the
functions in the Analysis ToolPak (pre Excel 2007) will not return arrays.

You can check this by using Tools/Formula Auditing/Evaluate formula and
observing when, with a multi-cell argument, an array is returned. Or you can
select the relevant part of the function in the function bar, and hit <f9 to
see the intermediate result.
--ron

Understanding SUMPRODUCT()
 

The help file refers to SUMPRODUCT as summing products. That is true, but it
can do other things such as;

A1 = 3, A2 = 5, A3 = 7, B1 = 2, B2 = 2 B3 = 2

C1: =SUMPRODUCT(A1:A3*B1:B3) result: 30

C2: =SUMPRODUCT(A1:A3/B1:B3) result: 7.5

C3: =SUMPRODUCT(A1:A3+B1:B3) result: 21

C4: =SUMPRODUCT(A1:A3-B1:B3) result: 9

C5: =SUMPRODUCT(A1:A3^B1:B3) result: 83



Tyro



"Jakobshavn Isbrae" wrote in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

I think he probably knows that, what he didn't know was nothing to do with
SP per se, but to do with why some functions can handles arrays and some
can't.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tyro" wrote in message
...
The help file refers to SUMPRODUCT as summing products. That is true, but
it can do other things such as;

A1 = 3, A2 = 5, A3 = 7, B1 = 2, B2 = 2 B3 = 2

C1: =SUMPRODUCT(A1:A3*B1:B3) result: 30

C2: =SUMPRODUCT(A1:A3/B1:B3) result: 7.5

C3: =SUMPRODUCT(A1:A3+B1:B3) result: 21

C4: =SUMPRODUCT(A1:A3-B1:B3) result: 9

C5: =SUMPRODUCT(A1:A3^B1:B3) result: 83



Tyro



"Jakobshavn Isbrae" wrote in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

I mention it only because none of the documentation I have ever seen about
SUMPRODUCT, including Excel help, mention that.

Tyro

"Bob Phillips" wrote in message
...
I think he probably knows that, what he didn't know was nothing to do with
SP per se, but to do with why some functions can handles arrays and some
can't.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tyro" wrote in message
...
The help file refers to SUMPRODUCT as summing products. That is true, but
it can do other things such as;

A1 = 3, A2 = 5, A3 = 7, B1 = 2, B2 = 2 B3 = 2

C1: =SUMPRODUCT(A1:A3*B1:B3) result: 30

C2: =SUMPRODUCT(A1:A3/B1:B3) result: 7.5

C3: =SUMPRODUCT(A1:A3+B1:B3) result: 21

C4: =SUMPRODUCT(A1:A3-B1:B3) result: 9

C5: =SUMPRODUCT(A1:A3^B1:B3) result: 83



Tyro



"Jakobshavn Isbrae" wrote in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

I should clarify. Excel help says SUMPRODUCT sums the products. Products are
produced only by multiplication. So, the Excel help file implies that
SUMPRODUCT does only multiplication.

Tyro

"Bob Phillips" wrote in message
...
I think he probably knows that, what he didn't know was nothing to do with
SP per se, but to do with why some functions can handles arrays and some
can't.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tyro" wrote in message
...
The help file refers to SUMPRODUCT as summing products. That is true, but
it can do other things such as;

A1 = 3, A2 = 5, A3 = 7, B1 = 2, B2 = 2 B3 = 2

C1: =SUMPRODUCT(A1:A3*B1:B3) result: 30

C2: =SUMPRODUCT(A1:A3/B1:B3) result: 7.5

C3: =SUMPRODUCT(A1:A3+B1:B3) result: 21

C4: =SUMPRODUCT(A1:A3-B1:B3) result: 9

C5: =SUMPRODUCT(A1:A3^B1:B3) result: 83



Tyro



"Jakobshavn Isbrae" wrote in
message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake

Understanding SUMPRODUCT()
 

The fact that he was trying

=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

suggests that he knows that, at least it does to me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tyro" wrote in message
...
I should clarify. Excel help says SUMPRODUCT sums the products. Products
are produced only by multiplication. So, the Excel help file implies that
SUMPRODUCT does only multiplication.

Tyro

"Bob Phillips" wrote in message
...
I think he probably knows that, what he didn't know was nothing to do with
SP per se, but to do with why some functions can handles arrays and some
can't.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tyro" wrote in message
...
The help file refers to SUMPRODUCT as summing products. That is true,
but it can do other things such as;

A1 = 3, A2 = 5, A3 = 7, B1 = 2, B2 = 2 B3 = 2

C1: =SUMPRODUCT(A1:A3*B1:B3) result: 30

C2: =SUMPRODUCT(A1:A3/B1:B3) result: 7.5

C3: =SUMPRODUCT(A1:A3+B1:B3) result: 21

C4: =SUMPRODUCT(A1:A3-B1:B3) result: 9

C5: =SUMPRODUCT(A1:A3^B1:B3) result: 83



Tyro



"Jakobshavn Isbrae" wrote
in message ...
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of
even
values between A1 and A20. The following attempts do not work:

=SUMPRODUCT(--(--ISEVEN(A1:A20)=1))
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE"))

any help will be appreciated.
--
jake