Counting values within a Date Range
 

I am trying to find a way to count the number of values for a specific user
defined date range. HELP!

Might help more if I am more specific. I have a column for order type (i.e,
Floor, Roof, Wall) then a column with the date that order was created. I
would like to be able to identify how many orders by "type" within a certain
date range. Is that possible? This would be something that I want to generate
every month so I would probably be using first day of month to last day of
month. Any help would be GREATLY appreciate!

"Jana" wrote:

I am trying to find a way to count the number of values for a specific user
defined date range. HELP!

Hi
use SUMPRODUCT. e.g.
=SUMPRODUCT(--(A1:A100="type1"),--(B1:B100=DATE(2004,1,1)),--(B1:B100<
=DATE(2004,12,31))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"Jana" schrieb im Newsbeitrag
...
I am trying to find a way to count the number of values for a
specific user
defined date range. HELP!

As an example:

=SUMPRODUCT(--(A2:A1000="Wall"),--
(B2:B1000="10/1/04"+0),--(B2:B1000<="10/31/04"+0))

Where col. A is the order type and col. B is the order
date. You can also substitute the text strings for cell
references:

=SUMPRODUCT(--(A2:A1000=T1),--(B2:B1000=U1),--
(B2:B1000<=V1))

HTH
Jason
Atlanta, GA

-----Original Message-----
Might help more if I am more specific. I have a column
for order type (i.e,
Floor, Roof, Wall) then a column with the date that
order was created. I
would like to be able to identify how many orders
by "type" within a certain
date range. Is that possible? This would be something
that I want to generate
every month so I would probably be using first day of
month to last day of
month. Any help would be GREATLY appreciate!

"Jana" wrote:

I am trying to find a way to count the number of
values for a specific user
defined date range. HELP!
.

I LOOOOOOOOOOOOOOOOOOOOOOOVVVVVVE YOU GUYS!!!!! Thank you so much!!! It
worked. You totally made my day!!!!!

"Jana" wrote:

I am trying to find a way to count the number of values for a specific user
defined date range. HELP!

I solved this using an array function, which allows you to nest
multiple conditions in a formula. Using your example, I created a list
starting in cell A1:
floor
roof
wall
floor
roof
wall
etc, down through cell A18.

Column B1:B18 I populated with dates, starting with 5/1/2004 and adding
3 days to each date so cell B2 = 5/4/2004 and cell B18 = 6/21/2004.

In cells D1:D3 I recreated the entries in column A such that each entry
is represented just once: D1 = floor; D2 = roof; D3 = wall.

In cell E1 I entered this formula:
=SUM(IF(D1=$A$1:$A$18,IF($B$1:$B$18=DATEVALUE("5/1/2004"),IF($B$1:$B$18<=DATEVALUE("6/1/2004"),1,0))))

Ordinarily you invoke a formula by pressing the [Enter] key. To invoke
the array function in cell E1, simultaneously press the [Ctrl] [Shift]
[Enter] keys.

This creates a multi-conditional "lookup": the English translation is
"Find the value in cell D1 (floor) in the range A1:A18, and if the date
in the corresponding row in column B is between May 1st and June 1st,
add 1."

If you prefer not to use the DATEVALUE() function to convert a text
string to a date, you can use a cell reference in the formula. Suppose
cells G1 and G2 contain the dates (formatted and entered as dates): the
formula is
=SUM(IF(D1=$A$1:$A$18,IF($B$1:$B$18=G1,IF($B$1:$B $18<=G2,1,0))))
Again, invoke the array function by simultaneously pressing the [Ctrl]
[Shift] [Enter] keys.

Hope this does it for you!
Dave O

Hey guys...what do the -- stand for? Trying to understand the formula

"Jana" wrote:

I am trying to find a way to count the number of values for a specific user
defined date range. HELP!

Hi
follow the link I provided or also have a look at:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Regards
Frank Kabel
Frankfurt, Germany

"Jana" schrieb im Newsbeitrag
...
Hey guys...what do the -- stand for? Trying to understand the formula

"Jana" wrote:

I am trying to find a way to count the number of values for a
specific user
defined date range. HELP!