Prev: Icon Sets - Display icon in one cell depending upon value in anoth
Next: Losing rows that reference data from another worsheet
From: Dave Peterson on 25 Feb 2010 18:56
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"))


Basenji wrote:
>
> 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?

--

Dave Peterson
From: Max on 25 Feb 2010 20:38
Thanks for the link. Looks like it was effort wasted here, then.
--
Max
Singapore
---
"T. Valko" wrote:
> Here's the original post:
>
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=05cb3708-3193-454b-a751-3a435a6eb019

First  |  Prev  | 
Pages: 1 2
Prev: Icon Sets - Display icon in one cell depending upon value in anoth
Next: Losing rows that reference data from another worsheet