Array formula acting as it shuld ... but

[DavidK]

I have the following array formula:

{=SUM(IF(MONTH('2008'!B$2:B$746)=$I4,1,0))}

In col I I have values 1 to 12. I would expect this to give me a
summary of monthly occurences based on column B which contains dates.

The problem is in the first month. It seems that if the cell is
blank, the ligical retuirns 1, so with nothing in column B the formula
returns a value of 45.

I have tried ISBLANK with no success, since the values in column B are
continually being added to.

All other months work except 01. Any suggestions?


David M. Kellerman

 

[Bob Phillips]

=SUM(IF((MONTH('2008'!B$2:B$746)=$I4)*('2008'!B$2:B$746<>""),1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


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

 

[T. Valko]

Try this non-array version:

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

 

[DavidK]

Biff,

Thanks and Happy New Year! This does the job ... now the qwuestion:
what are the -- in the formula? never saw those before.

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
---
HTH

Bob


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



Biff,

Thanks and Happy New Year! This does the job ... now the qwuestion:
what are the -- in the formula? never saw those before.

 

[DavidK]

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

 

[Bob Phillips]

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]

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)










- Show quoted text -

Much thanks for clarifying. Great lesson.