Error using SUMPRODUCT to count variable between a date range

[Chevy Nick]

I am using Excel 2003. Since I don't have COUNTIFS, I am using SUMPRODUCT to count the number of times that Column L (Change Type)="Mech" occurs during the month of March, 2011 (Column B is the date). See attached workbook; my formula is giving me an incorrect answer.

Here is the code I came up with:

Please Login or Register  to view this content.

In plain language, I am looking for the number of times that Column B is between 03/01/2011 and 03/31/2011 AND the value of Column L is equal to "Mech". My problem is that my answer should be 14, but my formula is giving me 13. Please see attached file.

What am I doing wrong? Is there a simpler or more elegant way of doing this?

I appreciate any assistance, Thanks.

[DonkeyOte]

You have a trailing space in L231 - use a TRIM as necessary.

[Marcol]

Try this

Please Login or Register  to view this content.

You have mixed US/UK date formats

[DonkeyOte]

Marcol, the date values in the original are fine given OP locale (03/01 & 03/31 being 1st March & 31st March respectively)

The issue as outlined relates to a trailing space causing the ="Mech" test to fail for one record.

The corrected version would look like:

Please Login or Register  to view this content.

or, if we assume monthly analysis at all times:

Please Login or Register  to view this content.

[Marcol]

@ DonkeyOteThat just dawned on me, a bit to late however!

[Marcol]

As an apology for my earlier blunder, I have "developed" Dons' second formula (Post #4).

This should work regardless of the machine date settings.

Select from the drop-downs A7:C7

All credits to DonkeyOte.

[Chevy Nick]

Thanks for your help. DonkeyOte; how did you find the trailing space in L231?

[martindwilson]

i dont know about donkyote but when you open your sample you can see l231 is aligned diferently
i'd wonder why and check it,