return based on range of dates

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

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

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

"Pete_UK" wrote:

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

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" wrote:

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

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

Pete

Originally Posted by Matt

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

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

"daddylonglegs" wrote:

>
> Matt Wrote:
> > 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
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=511912
>
>