sumproduct (month)

[ronnomad]

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 Smith]

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

 

[Jim Thomlinson]

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

 

[T. Valko]

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?

 

[Jim Thomlinson]

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.

 

[ronnomad]

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]

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

 

[T. Valko]

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.