Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jana
 
Posts: n/a
Default 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!
  #2   Report Post  
Jana
 
Posts: n/a
Default

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!

  #3   Report Post  
Frank Kabel
 
Posts: n/a
Default

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!


  #4   Report Post  
Jason Morin
 
Posts: n/a
Default

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!

.

  #5   Report Post  
Jana
 
Posts: n/a
Default

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!



  #6   Report Post  
Dave O
 
Posts: n/a
Default

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

  #7   Report Post  
Jana
 
Posts: n/a
Default

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!

  #8   Report Post  
Frank Kabel
 
Posts: n/a
Default

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!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date updates from worksheet to chart & changes date to a date series! Help!! Jayjg Charts and Charting in Excel 2 January 22nd 05 04:00 PM
Second serie doesn't use X-as values JackRnl Charts and Charting in Excel 1 January 20th 05 02:04 AM
Lookup the latest date in a range so it appears as my result FBB Excel Discussion (Misc queries) 1 December 4th 04 04:50 AM
How do I convert a Julian date into a regular date? Jessica Excel Discussion (Misc queries) 4 December 2nd 04 03:54 AM
Creating a Date Selector in Excel VBA? Mark Excel Discussion (Misc queries) 0 November 25th 04 11:59 PM


All times are GMT +1. The time now is 03:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"