Understanding SUMPRODUCT()

[Jakobshavn Isbrae]

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.

 

[Bernard Liengme]

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

 

[Mike H]

Several ways but using sumproduct try this

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

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

Mike

 

[Ron Rosenfeld]

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

 

[Jakobshavn Isbrae]

Thank you !
--
jake

Several ways but using sumproduct try this

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

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

Mike

 

[Mike H]

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]

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?

 

[Jakobshavn Isbrae]

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.

 

[Bob Phillips]

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]

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.

 

[Ron Rosenfeld]

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.

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

 

[Bob Phillips]

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]

Thanks Bob.
--
jake

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]

Thanks Ron
--
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

 

[Tyro]

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

 

[Bob Phillips]

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]

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

Tyro

 

[Tyro]

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]

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)

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