Count between two dates using sumproduct

[Basenji]

Using Excel 2003, A5:A1000 is a range of dates covering 12 months, 1/1/10
through 12/31/10; E5:E1000 is a range of names, ie Smith. I need to count the
number of times Smith occurs for each month, ie January, February, etc. I
have the following formula,
{=SUMPRODUCT(--(A5:A1000>=1/1/2010),--(A5:A1000<=1/31/2010),--(E5:E1000)="Smith")}, but am getting a value error. What am I missing?

 

[Max]

Try this amendment, normal ENTER will do:
=SUMPRODUCT(--(A5:A10>=--"1/1/2010"),--(A5:A10<=--"1/31/2010"),--(E5:E10="Smith"))
Success? wave it, hit YES below

 

[T. Valko]

What am I missing?

Well, for one thing, I explained in your other post why you shouldn't use
these expressions:

A5:A1000>=1/1/2010
A5:A1000<=1/31/2010

am getting a value error.

After reading your reply in the other post I'm thinking that your dates
(either all of them or some of them) aren't true Excel dates.

Does every cell in this range, A5:A1000, contain a date? If so, and if
they're true Excel dates then this formula:

=COUNT(A5:A1000)

Should return 996

 

[T. Valko]

Here's the original post:

http://www.microsoft.com/communitie...&p=1&tid=05cb3708-3193-454b-a751-3a435a6eb019

 

[Joe User]

I have the following formula,
{=SUMPRODUCT(--(A5:A1000>=1/1/2010),
--(A5:A1000<=1/31/2010),--(E5:E1000)="Smith")},
but am getting a value error. What am I missing?

The #VALUE error is caused by the incorrect placement of parentheses in the
last argument. But your formula will still have unexpected results.

At a minimum, you should write:

=SUMPRODUCT(--(A5:A1000>=--"1/1/2010"),
--(A5:A1000<=--"1/31/2010"),--(E5:E1000="Smith"))

This assumes that the dates in A5:A1000 are bona fide date numbers, not
text. The syntax --"1/1/2010" converts the date string to a date number;
otherwise, you are computing 1 divided by 1 divided by 2010. Also note the
placement of parentheses in the last argument.

Although that might work for you, it depends on your Regional and Language
settings. It would be better to write:

=SUMPRODUCT(--(A5:A1000>=DATE(2010,1,1)),
--(A5:A1000<=DATE(2010,1,31),--(E5:E1000="Smith"))

However, that still requires that you customize 12 different formulas. For
a more robust design, put the dates 1/1/2010, 2/1/2010 etc into a column, say
B1:B12, formatted with the Custom format "mmm" without quotes. Then in a
parallel column, say C1:C12, put the following formula into C1 and copy down:

=SUMPRODUCT(--(MONTH(A5:A1000)=MONTH(B1),
--(E5:E1000="Smith"))

or more simply:

=SUMPRODUCT((MONTH(A5:A1000)=MONTH(C1)
*(E5:E1000="Smith"))

This assumes that the dates in A5:A1000 are all in the same year, or at
least one 12-month period, as you stipulated originally.


----- original message -----

 

[Dave Peterson]

I would use the =date() function:

=SUMPRODUCT(--(A5:A1000>=date(2010,1,1)),
--(A5:A1000<=date(2010,1,31)),
--(E5:E1000="Smith"))

(watch your parentheses around that last portion)

(you don't need to array enter it either.)

And if you really wanted to check for a complete month:

=SUMPRODUCT(--(text(A5:A1000,"yyyymm")="201001"),
--(E5:E1000="Smith"))

 

[Max]

Thanks for the link. Looks like it was effort wasted here, then.