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?

.