# How do I create a formula that gives number records with certain d

A

#### apruett

I am trying to create a formula that looks at a list of records in a column
on another sheet in the same workbook and tells me how mnay records show a
date bewteen two specified dates. For example, I want to know how many
clients we had contact with in the first quarter of 2009.
Here is what I tried.
=SUM(IF('SO Youth'!\$G\$3:\$G\$476>=DATEVALUE("7/1/2008"),IF('SO
Youth'!\$G\$3:\$G\$476<DATEVALUE("10/1/2008"),1,0),0))
Even though I know there are 46 records with dates between those specified
in the formula, the result I am getting is zero.
Any thoughts?

M

#### Max

2 debug thoughts for you:
a. Check that the formula is correctly array-entered. In the formula bar, it
should appear within curly braces: ={ ... }.
b. Check the source "dates" data in SO Youth's col G. These need to be real
dates. If so, are there any actual real dates which fall within the date
range criteria specified in your expression?

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik

S

#### Satyendra_Haldaur

=SUM(IF(F1:F14>DATE(2009,7,8),IF(F1:F14<DATE(2009,7,17),1,0),0))
use it as array formula
you can use datevalue for date less than 1999

R

#### Ron Rosenfeld

I am trying to create a formula that looks at a list of records in a column
on another sheet in the same workbook and tells me how mnay records show a
date bewteen two specified dates. For example, I want to know how many
clients we had contact with in the first quarter of 2009.
Here is what I tried.
=SUM(IF('SO Youth'!\$G\$3:\$G\$476>=DATEVALUE("7/1/2008"),IF('SO
Youth'!\$G\$3:\$G\$476<DATEVALUE("10/1/2008"),1,0),0))
Even though I know there are 46 records with dates between those specified
in the formula, the result I am getting is zero.
Any thoughts?

Try this:

=countif('SO Youth'!\$G\$3:\$G\$476,">="&date(2008,7,1)) -
countif('SO Youth'!\$G\$3:\$G\$476,">"&date(2008,9,30))

--ron