Problems with the month on January..

**Hodged** · 11-13-2007, 11:37 AM

Hi everyone,

I've recently been creating a report to record calls people make during the year.

If you open up the attached file you'll see that you can enter a date in the appropiate section at the bottom and then this is recorded in the section at the top (between cells D12 and H33).

However I'm having a problem with the month of January.

For example, the formula I'm using to calculate the number of visits to band "A" customers in April is as follows:

=SUM(IF($D$29:$D$190="A",IF(MONTH($E$29:$CF$190)=4,1,0),0))

Where the "A" refers to the customer band and the "4" refers to April.

However, when I try to make the month equal to "1" (i.e January) very high numbers appear - even when no data is entered.

Why is this? And how can I fix it?!

Hopefully it should all be pretty clear when you open the attached file..

Thanks for any help at all!

**Richard Schollar** · 11-13-2007, 11:44 AM

Hi

=MONTH(A1)

returns 1 if A1 is empty - hence your problem You could amend your original formula to:

=SUM(IF($D$29:$D$190="A",IF(TEXT($E$29:$CF$190,"mmm")="Jan" ,1,0),0))

or

=SUM(IF($D$29:$D$190="A",IF($E$29:$CF$190<>"",IF(MONTH($E$29:$CF$190)=4 ,1,0),0)))

Best regards

Richard