return based on range of dates

[Matt]

I am trying to return a # of leads issued to a salesperson based on a range
of dates from their worksheet to a master worksheet.

the formula i have is: =COUNTIF('SalesPerson'!A2:A500,DATE(2006,1,24))

this works and returns all the leads issued on 1/24/2006 just fine. but i
want to return the # of leads for the entire MONTH (January in this case).
the worksheet i'm trying to pull from is set up:

A2 1/24/2006
A3 1/24/2006
A4 1/26/2006
A5 2/1/2006

 

[Pete_UK]

Try this:

=SUM(IF(MONTH(Date_range)=1,1,0))

This is an array formula so you must commit it with
<CTRL><SHIFT><enter> at the same time - if you do it correctly, Excel
will wrap curly braces { } around the formula.

Date_range is the range where your dates are, eg A1:A50 - substitute as
appropriate. This looks for dates where MONTH( ) = 1. You can have this
looking at a cell if you wish, so that you can change the value in the
cell for a different month.

Hope this helps.

Pete

 

[Matt]

hhmmm. this returns a value of 490 when it should be 7. in other words, there
are 7 rows with dates ranging from 1/1/2006-1/31/2006 in column A. there are
around 12 rows with dates ranging from 2/1/2006-2/28/2006 in column A. i'm
trying to extract ONLY the number of January rows from this sheet and put the
count in a "January" column i have set up in my master sheet. same for the
other months of the year.

does that make more sense? thanks for the help BTW

 

[Matt]

Wierd. After playing around, that formula works for February - December but
isn't for January. Thank you though. I am REALLY close now

 

[Pete_UK]

Thanks for the feedback - post back if you still have problems.

Pete

 

[daddylonglegs]

Wierd. After playing around, that formula works for February - December
but
isn't for January. Thank you though. I am REALLY close now

In Excel a blank cell - in date terms - is a date in January 1900 so
=MONTH(A1), when A1 is blank gives 1, i.e. January, hence your
problems. There are several other approaches which would eliminate this
problem, one is

=COUNTIF('SalesPerson'!A2:A500,">="&DATE(2006,1,1))-COUNTIF('SalesPerson'!A2:A500,">"&DATE(2006,1,31))

or for a more generic approach put the 1st day of the month you
require, e.g. 01/01/2006 in a cell, e.g. H2 and use

=COUNTIF('SalesPerson'!A2:A500,">="&H2)-COUNTIF('SalesPerson'!A2:A500,">"&EOMONTH(H2,0))

Note:EOMONTH requires Analysis ToolPak

 

[Matt]

That did IT!! would only work with the Generic version though for some reason
(=COUNTIF('SalesPerson'!A2:A500,">="&H2)-COUNTIF('SalesPerson'!A2:A500,">"&EOMONTH(H2,0)))

Thanks a TON