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

return based on range of dates
 

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

return based on range of dates
 

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

return based on range of dates
 

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

return based on range of dates
 

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

Pete

return based on range of dates
 

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

return based on range of dates
 

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