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?
 
Ad

Advertisements

M

Max

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
 
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
 
Ad

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top