Counting number of times name occurs between two dates
 

Using Excel 2003 I have this formula to count the number of times the
physicians name occurs between two dates:
=SUMPRODUCT(--(Date=3/1/2010),--(Date<=3/31/2010),--(Physicians="Smith")).
Date refers to a named range of cells in column A. Physicians refers to a
named range of cells in column E. I am getting this value error message, "A
value used in the formula is of the wrong data type." What needs to be
changed? Thank you for your assistance.

Counting number of times name occurs between two dates
 

=SUMPRODUCT(--(Date=3/1/2010),--(Date<=3/31/2010),--(Physicians="Smith"))

As written, your formula is evaluating these:

3/1/2010
3/31/2010

as:

3 divided by 1 divided by 2010
3 divided by 31 divided by 2010

Either use cells to hold the dates:

A1 = 3/1/2010
B1 = 3/31/2010

=SUMPRODUCT(--(Date=A1),--(Date<=B1),--(Physicians="Smith"))

Or, use the DATE function:

=SUMPRODUCT(--(Date=DATE(2010,3,1)),--(Date<=DATE(2010,3,31)),--(Physicians="Smith"))

Or, use the TEXT function and eliminate one array of tests:

=SUMPRODUCT(--(TEXT(Date,"myyyy")="32010"),--(Physicians="Smith"))

However, I don't know if any of that has anything to do with the message
you're getting. So, try the new formulas and see what happens.

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Using Excel 2003 I have this formula to count the number of times the
physicians name occurs between two dates:
=SUMPRODUCT(--(Date=3/1/2010),--(Date<=3/31/2010),--(Physicians="Smith")).
Date refers to a named range of cells in column A. Physicians refers to a
named range of cells in column E. I am getting this value error message,
"A
value used in the formula is of the wrong data type." What needs to be
changed? Thank you for your assistance.

Counting number of times name occurs between two dates
 

Thank you for the explanation for the formula that I was trying to use. I
used the suggested date function but am still getting a value error message.

"T. Valko" wrote:

=SUMPRODUCT(--(Date=3/1/2010),--(Date<=3/31/2010),--(Physicians="Smith"))

As written, your formula is evaluating these:

3/1/2010
3/31/2010

as:

3 divided by 1 divided by 2010
3 divided by 31 divided by 2010

Either use cells to hold the dates:

A1 = 3/1/2010
B1 = 3/31/2010

=SUMPRODUCT(--(Date=A1),--(Date<=B1),--(Physicians="Smith"))

Or, use the DATE function:

=SUMPRODUCT(--(Date=DATE(2010,3,1)),--(Date<=DATE(2010,3,31)),--(Physicians="Smith"))

Or, use the TEXT function and eliminate one array of tests:

=SUMPRODUCT(--(TEXT(Date,"myyyy")="32010"),--(Physicians="Smith"))

However, I don't know if any of that has anything to do with the message
you're getting. So, try the new formulas and see what happens.

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Using Excel 2003 I have this formula to count the number of times the
physicians name occurs between two dates:
=SUMPRODUCT(--(Date=3/1/2010),--(Date<=3/31/2010),--(Physicians="Smith")).
Date refers to a named range of cells in column A. Physicians refers to a
named range of cells in column E. I am getting this value error message,
"A
value used in the formula is of the wrong data type." What needs to be
changed? Thank you for your assistance.


.