sumproduct (month)

I am using the following =SUMPRODUCT(--(MONTH(A$3:A$87)=1)) to count the
number of transactions for the time period. When I change the Month number
to 2 the formula calculates correctly. However, for Month = 1, the formula
is counting all the blank cells in the column as 1 also (I saw this using the
formula auditing feature). A fix would be appreciated but I would also like
to know why?

On a related topic, is there a way to use the Month feature to determing Max
& Min values for the specified time period?

A blank cell has the value 0. The date 0 is Jan 0, 1900. The month number
for this date is 1.

Amend your formula to:
=SUMPRODUCT(--(MONTH(A$3:A$87)=1),--(A$3:A$87<>""))

I don't understand your related topic. Have you got an example?

Regards
Fred

"ronnomad" > wrote in message
...
>I am using the following =SUMPRODUCT(--(MONTH(A$3:A$87)=1)) to count the
> number of transactions for the time period. When I change the Month
> number
> to 2 the formula calculates correctly. However, for Month = 1, the
> formula
> is counting all the blank cells in the column as 1 also (I saw this using
> the
> formula auditing feature). A fix would be appreciated but I would also
> like
> to know why?
>
> On a related topic, is there a way to use the Month feature to determing
> Max
> & Min values for the specified time period?

A blank cell is the same as a cell with 0 in it. Dates are stored as the
number of days that have elapsed since jan 1, 1900. So day 0 is Jan 1, 1900.
That is why your count is off for January. Add in a criteria to exclude 0.

=SUMPRODUCT(--(MONTH(A$3:A$87)=1), --(A$3:A$87<> ""))

--
HTH...

Jim Thomlinson


"ronnomad" wrote:

> I am using the following =SUMPRODUCT(--(MONTH(A$3:A$87)=1)) to count the
> number of transactions for the time period. When I change the Month number
> to 2 the formula calculates correctly. However, for Month = 1, the formula
> is counting all the blank cells in the column as 1 also (I saw this using the
> formula auditing feature). A fix would be appreciated but I would also like
> to know why?
>
> On a related topic, is there a way to use the Month feature to determing Max
> & Min values for the specified time period?

>So day 0 is Jan 1, 1900.

This is a very difficult thing to explain!

Day 0 is actually Dec 31 1899 but as we know Excel doesn't recognize dates
before Jan 1 1900. However, you can calculate a "day 0" but you can't enter
the *date* Jan 0 1900.

According to Excel, Jan 1 1900 is a Sunday so logic tells us that Dec 31
1899 (also known as Jan 0 1900) must be a Saturday. That's why you get Sat
when you reference an empty cell with this formula:

=TEXT(A1,"ddd")

You can't enter the *date* 1/0/1900 in a cell but you can use a formula to
do so:

=DATE(1900,1,0)

So, "day 0" refers to the last day of the previous month *except* when the
MONTH function refers to numeric 0 or an empty cell.

=DATE(2010,1,0) = 12/31/2009
=MONTH(DATE(2010,1,0)) = 12

=MONTH(0) = 1
=MONTH(empty_cell) = 1
=MONTH(DATE(1900,1,0)) = 1

0 formatted as date = 1/0/1900

Doesn't make a bit of sense, does it?

--
Biff
Microsoft Excel MVP


"Jim Thomlinson" > wrote in message
...
>A blank cell is the same as a cell with 0 in it. Dates are stored as the
> number of days that have elapsed since jan 1, 1900. So day 0 is Jan 1,
> 1900.
> That is why your count is off for January. Add in a criteria to exclude 0.
>
> =SUMPRODUCT(--(MONTH(A$3:A$87)=1), --(A$3:A$87<> ""))
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "ronnomad" wrote:
>
>> I am using the following =SUMPRODUCT(--(MONTH(A$3:A$87)=1)) to count the
>> number of transactions for the time period. When I change the Month
>> number
>> to 2 the formula calculates correctly. However, for Month = 1, the
>> formula
>> is counting all the blank cells in the column as 1 also (I saw this using
>> the
>> formula auditing feature). A fix would be appreciated but I would also
>> like
>> to know why?
>>
>> On a related topic, is there a way to use the Month feature to determing
>> Max
>> & Min values for the specified time period?

Thanks Biff. I never noticed that before. I just always assumed that they
started counting from 0 and not from 1. Computers like to do that... oddly
enough I was corrent although I was entirely wrong.
--
HTH...

Jim Thomlinson


"T. Valko" wrote:

> >So day 0 is Jan 1, 1900.
>
> This is a very difficult thing to explain!
>
> Day 0 is actually Dec 31 1899 but as we know Excel doesn't recognize dates
> before Jan 1 1900. However, you can calculate a "day 0" but you can't enter
> the *date* Jan 0 1900.
>
> According to Excel, Jan 1 1900 is a Sunday so logic tells us that Dec 31
> 1899 (also known as Jan 0 1900) must be a Saturday. That's why you get Sat
> when you reference an empty cell with this formula:
>
> =TEXT(A1,"ddd")
>
> You can't enter the *date* 1/0/1900 in a cell but you can use a formula to
> do so:
>
> =DATE(1900,1,0)
>
> So, "day 0" refers to the last day of the previous month *except* when the
> MONTH function refers to numeric 0 or an empty cell.
>
> =DATE(2010,1,0) = 12/31/2009
> =MONTH(DATE(2010,1,0)) = 12
>
> =MONTH(0) = 1
> =MONTH(empty_cell) = 1
> =MONTH(DATE(1900,1,0)) = 1
>
> 0 formatted as date = 1/0/1900
>
> Doesn't make a bit of sense, does it?
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Jim Thomlinson" > wrote in message
> ...
> >A blank cell is the same as a cell with 0 in it. Dates are stored as the
> > number of days that have elapsed since jan 1, 1900. So day 0 is Jan 1,
> > 1900.
> > That is why your count is off for January. Add in a criteria to exclude 0.
> >
> > =SUMPRODUCT(--(MONTH(A$3:A$87)=1), --(A$3:A$87<> ""))
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "ronnomad" wrote:
> >
> >> I am using the following =SUMPRODUCT(--(MONTH(A$3:A$87)=1)) to count the
> >> number of transactions for the time period. When I change the Month
> >> number
> >> to 2 the formula calculates correctly. However, for Month = 1, the
> >> formula
> >> is counting all the blank cells in the column as 1 also (I saw this using
> >> the
> >> formula auditing feature). A fix would be appreciated but I would also
> >> like
> >> to know why?
> >>
> >> On a related topic, is there a way to use the Month feature to determing
> >> Max
> >> & Min values for the specified time period?
>
>
> .
>

Fred,

Counting the number of events and even averaging numbers is easy. What I
was looking for was a way to determine a Max or Min based on the Month
number.

"Fred Smith" wrote:

> A blank cell has the value 0. The date 0 is Jan 0, 1900. The month number
> for this date is 1.
>
> Amend your formula to:
> =SUMPRODUCT(--(MONTH(A$3:A$87)=1),--(A$3:A$87<>""))
>
> I don't understand your related topic. Have you got an example?
>
> Regards
> Fred
>
> "ronnomad" > wrote in message
> ...
> >I am using the following =SUMPRODUCT(--(MONTH(A$3:A$87)=1)) to count the
> > number of transactions for the time period. When I change the Month
> > number
> > to 2 the formula calculates correctly. However, for Month = 1, the
> > formula
> > is counting all the blank cells in the column as 1 also (I saw this using
> > the
> > formula auditing feature). A fix would be appreciated but I would also
> > like
> > to know why?
> >
> > On a related topic, is there a way to use the Month feature to determing
> > Max
> > & Min values for the specified time period?
>
> .
>

I don't have a suggestion for the related matter of Max or Min.

I suggest you post a new thread (to bring it to the top of the list) with
that specific question.

Regards,
Fred

"ronnomad" > wrote in message
...
> Fred,
>
> Counting the number of events and even averaging numbers is easy. What I
> was looking for was a way to determine a Max or Min based on the Month
> number.
>
> "Fred Smith" wrote:
>
>> A blank cell has the value 0. The date 0 is Jan 0, 1900. The month number
>> for this date is 1.
>>
>> Amend your formula to:
>> =SUMPRODUCT(--(MONTH(A$3:A$87)=1),--(A$3:A$87<>""))
>>
>> I don't understand your related topic. Have you got an example?
>>
>> Regards
>> Fred
>>
>> "ronnomad" > wrote in message
>> ...
>> >I am using the following =SUMPRODUCT(--(MONTH(A$3:A$87)=1)) to count
>> >the
>> > number of transactions for the time period. When I change the Month
>> > number
>> > to 2 the formula calculates correctly. However, for Month = 1, the
>> > formula
>> > is counting all the blank cells in the column as 1 also (I saw this
>> > using
>> > the
>> > formula auditing feature). A fix would be appreciated but I would also
>> > like
>> > to know why?
>> >
>> > On a related topic, is there a way to use the Month feature to
>> > determing
>> > Max
>> > & Min values for the specified time period?
>>
>> .
>>

Try these array formula** :

A1:A10 = dates
B1:B10 = values

=MAX(IF(MONTH(A1:A10)=N,B1:B10))

=MIN(IF(MONTH(A1:A10)=N,B1:B10))

To account for empty cells in the date range:

=MAX(IF(ISNUMBER(A1:A10),IF(MONTH(A1:A10)=N,B1:B10 )))

=MIN(IF(ISNUMBER(A1:A10),IF(MONTH(A1:A10)=N,B1:B10 )))

Where N = the month number from 1 to 12. 1 = Jan, 2 = Feb, 3 = Mar ... 12 =
Dec

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"ronnomad" > wrote in message
...
> Fred,
>
> Counting the number of events and even averaging numbers is easy. What I
> was looking for was a way to determine a Max or Min based on the Month
> number.
>
> "Fred Smith" wrote:
>
>> A blank cell has the value 0. The date 0 is Jan 0, 1900. The month number
>> for this date is 1.
>>
>> Amend your formula to:
>> =SUMPRODUCT(--(MONTH(A$3:A$87)=1),--(A$3:A$87<>""))
>>
>> I don't understand your related topic. Have you got an example?
>>
>> Regards
>> Fred
>>
>> "ronnomad" > wrote in message
>> ...
>> >I am using the following =SUMPRODUCT(--(MONTH(A$3:A$87)=1)) to count
>> >the
>> > number of transactions for the time period. When I change the Month
>> > number
>> > to 2 the formula calculates correctly. However, for Month = 1, the
>> > formula
>> > is counting all the blank cells in the column as 1 also (I saw this
>> > using
>> > the
>> > formula auditing feature). A fix would be appreciated but I would also
>> > like
>> > to know why?
>> >
>> > On a related topic, is there a way to use the Month feature to
>> > determing
>> > Max
>> > & Min values for the specified time period?
>>
>> .
>>