error with smproduct for January
 

Hi,

These formulas: "=SUMPRODUCT(--(MONTH(Calc!C4:Calc!C65536)
=B4))" and "=SUMPRODUCT(--(MONTH(Calc!C4:Calc!C65536)
=B4),Calc!M4:Calc!M65536)" work perfect for all months
except for January. The result if the month in C3 =1 is
always wrong. Columns "C"and"M"are updating periodically
by inserting a new row at the top of them. Can you help
me to fix this problem?

A small correction i insert the month in B4 not in C3.
Tim

It would be helpful to have a defined name range or use INDIRECT.

--
Don Guillett
SalesAid Software

"Tim" wrote in message
...
Hi,

These formulas: "=SUMPRODUCT(--(MONTH(Calc!C4:Calc!C65536)
=B4))" and "=SUMPRODUCT(--(MONTH(Calc!C4:Calc!C65536)
=B4),Calc!M4:Calc!M65536)" work perfect for all months
except for January. The result if the month in C3 =1 is
always wrong. Columns "C"and"M"are updating periodically
by inserting a new row at the top of them. Can you help
me to fix this problem?

Having empty cells in that range will cause trouble.

If you leave A1 empty, then put =month(a1), you'll see excel returns 1.

So you could check to see if the cell is non-empty in your formula.

=SUMPRODUCT(--(MONTH(calc!C4:C65536)=B4),--(calc!C4:C65536<""))

I wouldn't use this style: calc!c4:calc!c65536.

And if possible, I'd try to limit that range to something smaller (but large
enough!).




Tim wrote:

Hi,

These formulas: "=SUMPRODUCT(--(MONTH(Calc!C4:Calc!C65536)
=B4))" and "=SUMPRODUCT(--(MONTH(Calc!C4:Calc!C65536)
=B4),Calc!M4:Calc!M65536)" work perfect for all months
except for January. The result if the month in C3 =1 is
always wrong. Columns "C"and"M"are updating periodically
by inserting a new row at the top of them. Can you help
me to fix this problem?

--

Dave Peterson

Just to add...

if A1 is empty, then =a1 will return a 0.

Excel keeps track of dates by counting the number of days since a base date
(usually Dec 31, 1899 for windows users).

If you put 0 in a cell, then format it as a custom date: dddd, mmmm dd, yyyy
You'll see:
Saturday, January 00, 1900

So empty cells/0 values are in January (well, for excel anyway!)



Dave Peterson wrote:

Having empty cells in that range will cause trouble.

If you leave A1 empty, then put =month(a1), you'll see excel returns 1.

So you could check to see if the cell is non-empty in your formula.

=SUMPRODUCT(--(MONTH(calc!C4:C65536)=B4),--(calc!C4:C65536<""))

I wouldn't use this style: calc!c4:calc!c65536.

And if possible, I'd try to limit that range to something smaller (but large
enough!).

Tim wrote:

Hi,

These formulas: "=SUMPRODUCT(--(MONTH(Calc!C4:Calc!C65536)
=B4))" and "=SUMPRODUCT(--(MONTH(Calc!C4:Calc!C65536)
=B4),Calc!M4:Calc!M65536)" work perfect for all months
except for January. The result if the month in C3 =1 is
always wrong. Columns "C"and"M"are updating periodically
by inserting a new row at the top of them. Can you help
me to fix this problem?

--

Dave Peterson

--

Dave Peterson