Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula that will count between dates ranges
I am looking for a formula that will look at cell B1 with a specific date
and only will count/add data from a column based on the date ranges provided August 04-August 05. For example I only want to count data between august 04 to august 05 Column A Column B Column C Column D jan-04 August 1 2004 1 Total Between August 04 and August 05= February-04 2 8 Mar-04 5 Aprl-04... 1 Aug-04 3 Sept-04 1 Oct-04 1 Nov-04... 1 Aug-05 2 |
#2
|
|||
|
|||
For summing...
=SUMIF(DateRange,"="&X2,SumRange)-SUMIF(DateRange,""&Y2,SumRange) jbsand1001 wrote: I am looking for a formula that will look at cell B1 with a specific date and only will count/add data from a column based on the date ranges provided August 04-August 05. For example I only want to count data between august 04 to august 05 Column A Column B Column C Column D jan-04 August 1 2004 1 Total Between August 04 and August 05= February-04 2 8 Mar-04 5 Aprl-04... 1 Aug-04 3 Sept-04 1 Oct-04 1 Nov-04... 1 Aug-05 2 |
#3
|
|||
|
|||
Aladin,
Thank you but I am having a little trouble with this formula. Date Range? Is this a range like January, February, etc... in consecutive cell columns? Can this be represented like C33:C36? &x2? What dos this do in this function? SumRange--Is this just the range like A d33:d36 Thank You, Judd "Aladin Akyurek" wrote: For summing... =SUMIF(DateRange,"="&X2,SumRange)-SUMIF(DateRange,""&Y2,SumRange) jbsand1001 wrote: I am looking for a formula that will look at cell B1 with a specific date and only will count/add data from a column based on the date ranges provided August 04-August 05. For example I only want to count data between august 04 to august 05 Column A Column B Column C Column D jan-04 August 1 2004 1 Total Between August 04 and August 05= February-04 2 8 Mar-04 5 Aprl-04... 1 Aug-04 3 Sept-04 1 Oct-04 1 Nov-04... 1 Aug-05 2 |
#4
|
|||
|
|||
Date range is a range with numeric dates, the sum range is the range you
want to sum assume you have dates in C33:C36 and values in D33:D36 and the criteria and X2 and Y2 holds the dates you want as criteria =SUMIF(C33:C36,"="&X2,D33:D36)-SUMIF(C33:C36,""&Y2,D33:D36) if you don't use the ampersand in countif or sumif they will look for the text string X2 as opposed to what's in cell X2 -- Regards, Peo Sjoblom "jbsand1001" wrote in message ... Aladin, Thank you but I am having a little trouble with this formula. Date Range? Is this a range like January, February, etc... in consecutive cell columns? Can this be represented like C33:C36? &x2? What dos this do in this function? SumRange--Is this just the range like A d33:d36 Thank You, Judd "Aladin Akyurek" wrote: For summing... =SUMIF(DateRange,"="&X2,SumRange)-SUMIF(DateRange,""&Y2,SumRange) jbsand1001 wrote: I am looking for a formula that will look at cell B1 with a specific date and only will count/add data from a column based on the date ranges provided August 04-August 05. For example I only want to count data between august 04 to august 05 Column A Column B Column C Column D jan-04 August 1 2004 1 Total Between August 04 and August 05= February-04 2 8 Mar-04 5 Aprl-04... 1 Aug-04 3 Sept-04 1 Oct-04 1 Nov-04... 1 Aug-05 2 |
#5
|
|||
|
|||
Thank You Peo, but I am still having a little trouble getting my arms around
this. &x2 ---I understand that if you don't put the & then it will go and look at cell X2. My question is should this "X2" be represented by the date (i.e. January) that I want this function to compare against? &Y2--I understand that if you don't put the & then it will go and look at cell Y2. My question is should this "Y2" be represented by the date (i.e. February) that I don't want this funciton to add into my total? If I am thinking right this function will look at column C33:C36 that have my dates and say if C33:C36 is greater than or equal to "January" then sum column D33:D36 that are adjacent to cells C33:C36. Then Look at C33:C36 and if C33:C36 is greater than January like February then subtract column D33:D36 from my total? Thank you, Judd "jbsand1001" wrote: I am looking for a formula that will look at cell B1 with a specific date and only will count/add data from a column based on the date ranges provided August 04-August 05. For example I only want to count data between august 04 to august 05 Column A Column B Column C Column D jan-04 August 1 2004 1 Total Between August 04 and August 05= February-04 2 8 Mar-04 5 Aprl-04... 1 Aug-04 3 Sept-04 1 Oct-04 1 Nov-04... 1 Aug-05 2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula that will count between dates ranges | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Formatting dates in a formula | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Count If Formula | Excel Worksheet Functions |