Counting number of times name occurs between two dates

[Basenji]

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.

 

[T. Valko]

=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.

 

[Basenji]

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.