Array formula acting as it shuld ... but
 

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

Array formula acting as it shuld ... but
 

=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)



"DavidK" wrote in message
...
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

Array formula acting as it shuld ... but
 

Try this non-array version:

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

--
Biff
Microsoft Excel MVP


"DavidK" wrote in message
...
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

Array formula acting as it shuld ... but
 

Biff,

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

On Dec 31 2007, 1:35*pm, "T. Valko" wrote:
Try this non-array version:

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

--
Biff
Microsoft Excel MVP

"DavidK" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Array formula acting as it shuld ... but
 

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)



"DavidK" wrote in message
...
Biff,

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

On Dec 31 2007, 1:35 pm, "T. Valko" wrote:
Try this non-array version:

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

--
Biff
Microsoft Excel MVP

"DavidK" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Array formula acting as it shuld ... but
 

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

Array formula acting as it shuld ... but
 

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

Array formula acting as it shuld ... but
 

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.