On Jan 3, 6:30*am, "Bob Phillips" wrote:
I don't understand what you mean by the statement ... Therefore,
(--(ISNUMBER('2008'!B$2:B$746)) should work as well as
((ISNUMBER('2008'!B$2:B$746)). *And it does.

The first returns an array of 1/0, whereas the latter returns an array of
TRUE/FALSE, so they are not the same.

In the same way, --(MONTH('2008'!B$2:B$746)=$I4) returns an array of 1/0,
whereas *(MONTH('2008'!B$2:B$746)=$I4)) returns an array of TRUE/FALSE..

The double unary is used to coerce arrays of TRUE/FALSE to corresponding
arrays of 1/0, which the PRODUCT part of SUMPRODUCT does its work on.

--
---
HTH

Bob

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

"DavidK" wrote in message

...



Bob,
I read the article, which identifies the use of the double unary. *I
even understand what the double unary is.

What I do not understand is the function of the double unary in the
formula:

=SUMPRODUCT(--(ISNUMBER('2008'!B$2:B$746)),--(MONTH('2008'!B$2:B$746)=
$I4))

Maybe my understanding is flawed.
I see this as creating an array of 0's for every blank cell in B2:B746
and 1's where there is a numeric value(date being stored as a
serialized number). Therefore, (--(ISNUMBER('2008'!B$2:B$746)) should
work as well as
((ISNUMBER('2008'!B$2:B$746)). *And it does.

What confuses me is the second array. *--(MONTH('2008'!B$2:B$746)=$I4)
should do the same, creating a 1 or 0 to be multiplied by the
corresponding value from the ISNUMBER array. *I do not see why
(MONTH('2008'!B$2:B$746)=$I4)) does not produce the same result
without the operator. Is it because it is a logical value versus
numeric?

Regardless, thank you for the introduction to SUMPRODUCT.

David- Hide quoted text -

- Show quoted text -

Much thanks for clarifying. Great lesson.