Prev: Icon Sets - Display icon in one cell depending upon value in anoth
Next: Losing rows that reference data from another worsheet
From: Basenji on 25 Feb 2010 17:05
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?
From: Max on 25 Feb 2010 17:47
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
--
Max
Singapore
---
"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?
From: T. Valko on 25 Feb 2010 17:57
>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

--
Biff
Microsoft Excel MVP


"Basenji" <Basenji(a)discussions.microsoft.com> wrote in message
news:210EB696-505C-4371-81DC-86851CC3EFEA(a)microsoft.com...
> 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?


From: T. Valko on 25 Feb 2010 18:03
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

--
Biff
Microsoft Excel MVP


"Max" <demechanik(a)yahoo.com> wrote in message
news:99101FB5-F8BF-45CC-ABF1-1874723A4BB5(a)microsoft.com...
> 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
> --
> Max
> Singapore
> ---
> "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?


From: Joe User on 25 Feb 2010 18:06
"Basenji" wrote:
> 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 -----

"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?
 |  Next  |  Last
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