question regarding formula

T

TLAngelo

I have an excel 2003 spreadsheet that has two worksheets in it. The second
worksheet calculates how many boxes were finished per month by each
individual from the first worksheet. The formula I am using is:

=SUMPRODUCT(--('Box Status'!$D$3:$D$31="BG"),--(MONTH('Box
Status'!$J$3:$J$31)=10))

So, it is supposed to count how many instances there are of the initials in
D3 through D31 (each row has a different set of initials) and for that
individual how many were completed in October. It seems to work except in a
couple of instances. January is all messed up. It is counting boxes being
finished in January for some people when they didn't finish any!

I thought at first it was because the dates are formatted as 01/29/08 and
the formula for January says =1, but it doesn't seem to make any difference.

Does anyone have any suggestions?

Tania
 
S

Sandy Mann

MONTH() will return 1 for empty cells because a date is just a number and XL
*thinks* that day zero was in January 1900.

For January add aother condition to the formula:

=SUMPRODUCT(--('Box Status'!$D$3:$D$31="BG"),--('Box
Status'!$J$3:$J$31)<>"")--(MONTH('Box Status'!$J$3:$J$31)=1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
P

Peo Sjoblom

Maybe they have empty cells, they are seen as January 0 1900 so you need to
add

,--(ISNUMBER('Box Status'!$J$3:$J$31))


into your formula


--


Regards,


Peo Sjoblom
 
T

TLAngelo

This one worked perfectly! Thank you!

Peo Sjoblom said:
Maybe they have empty cells, they are seen as January 0 1900 so you need to
add

,--(ISNUMBER('Box Status'!$J$3:$J$31))


into your formula


--


Regards,


Peo Sjoblom
Click to expand...
 
T

TLAngelo

This didn't work Sandy, but the formula that Peo Sjoblom provided did...
thank you for your help!

Tania
 
Top