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

=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" <(E-Mail Removed)> wrote in message
news:a29df558-8ec2-41fb-ae4a-(E-Mail Removed)...
>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

Try this non-array version:

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

--
Biff
Microsoft Excel MVP


"DavidK" <(E-Mail Removed)> wrote in message
news:a29df558-8ec2-41fb-ae4a-(E-Mail Removed)...
>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

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" <biffinp...@comcast.net> 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" <kellermanda...@netscape.net> wrote in message
>
> news:a29df558-8ec2-41fb-ae4a-(E-Mail Removed)...
>
>
>
> >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 -

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" <(E-Mail Removed)> wrote in message
news:6fd729fe-3ead-4d5e-95b5-(E-Mail Removed)...
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" <biffinp...@comcast.net> 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" <kellermanda...@netscape.net> wrote in message
>
> news:a29df558-8ec2-41fb-ae4a-(E-Mail Removed)...
>
>
>
> >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 -

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

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" <(E-Mail Removed)> wrote in message
news:6181d093-e63e-448f-812d-(E-Mail Removed)...
> 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

On Jan 3, 6:30*am, "Bob Phillips" <bob....@somewhere.com> 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" <kellermanda...@netscape.net> wrote in message
>
> news:6181d093-e63e-448f-812d-(E-Mail Removed)...
>
>
>
> > 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.