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?

Your formula looks ok.

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
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"apruett" wrote:
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?

=SUM(IF(F1:F14DATE(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
"apruett" wrote:

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?

On Tue, 7 Jul 2009 15:29:01 -0700, apruett
wrote:

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