Home |
Search |
Today's Posts |
#1
|
|||
|
|||
counting occurances
Hi,
I can't figure out how to count how many occurances fall between two dates. For example, how many of the dates in a column are Jan 01, 2003 and < Jan 01, 2004. COUNTIF will count one occurance only, so I think that I have to use maybe SUMPRODUCT but am now sure how the formula would work. Any ideas? Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting occurances
SR89
=SUMPRODUCT(--(A1:A20B1),--(A1:A20<B2)) I assumed that your dates are between A1:A20 and that the earlier date is in B1 and the later date is in B2. Please adjust formula to fit your data. Confirm with ctrl+shft+enter Let me know if this works for you. Dave "SR89" wrote in message ... Hi, I can't figure out how to count how many occurances fall between two dates. For example, how many of the dates in a column are Jan 01, 2003 and < Jan 01, 2004. COUNTIF will count one occurance only, so I think that I have to use maybe SUMPRODUCT but am now sure how the formula would work. Any ideas? Thanks, -- SR89 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting occurances
For a list of dates in A1:A100
You have a couple options: These formulas EXCLUDE 01/01/2003.... B1: =SUMPRODUCT((A1:A100DATEVALUE("01/01/2003"))*(A1:A100<DATEVALUE("01/01/2004"))) or B1: =COUNTIF(A1:A100,"<01/01/2004")-COUNTIF(A1:A100,"<=01/01/2003") If you want the count of any dates within 2003.... B1: =SUMPRODUCT(--(YEAR(A1:A100)=2003)) Does that help? *********** Regards, Ron XL2002, WinXP "SR89" wrote: Hi, I can't figure out how to count how many occurances fall between two dates. For example, how many of the dates in a column are Jan 01, 2003 and < Jan 01, 2004. COUNTIF will count one occurance only, so I think that I have to use maybe SUMPRODUCT but am now sure how the formula would work. Any ideas? Thanks, -- SR89 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting occurances
Hi
try =sumproduct(--($E$6:$E$83=$I$5)*($E$6:$E$83<=$I$6)) assuming that the range are on E6:e83 and I5 01/01/2003 and I6 01/01/2004 hth regards from Brazil Marcelo "SR89" escreveu: Hi, I can't figure out how to count how many occurances fall between two dates. For example, how many of the dates in a column are Jan 01, 2003 and < Jan 01, 2004. COUNTIF will count one occurance only, so I think that I have to use maybe SUMPRODUCT but am now sure how the formula would work. Any ideas? Thanks, -- SR89 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting occurances
For the between scenario, this formula is a bit shorter:
B1: =SUMPRODUCT((A1:A100(--"01/01/2003"))*(A1:A100<(--("01/01/2004")))) Easiest, though, is to enter the min and max dates in separate cells and reference them... B1: (min date to include) B2: (max date to include) Count of dates between those 2 dates, inclusive: B3: =SUMPRODUCT((A1:A100=B1)*(A1:A100<=B2)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: For a list of dates in A1:A100 You have a couple options: These formulas EXCLUDE 01/01/2003.... B1: =SUMPRODUCT((A1:A100DATEVALUE("01/01/2003"))*(A1:A100<DATEVALUE("01/01/2004"))) or B1: =COUNTIF(A1:A100,"<01/01/2004")-COUNTIF(A1:A100,"<=01/01/2003") If you want the count of any dates within 2003.... B1: =SUMPRODUCT(--(YEAR(A1:A100)=2003)) Does that help? *********** Regards, Ron XL2002, WinXP "SR89" wrote: Hi, I can't figure out how to count how many occurances fall between two dates. For example, how many of the dates in a column are Jan 01, 2003 and < Jan 01, 2004. COUNTIF will count one occurance only, so I think that I have to use maybe SUMPRODUCT but am now sure how the formula would work. Any ideas? Thanks, -- SR89 |
#6
|
|||
|
|||
Quote:
I have to find the number of occurances between two dates as mentioned. This database will be getting updated on a continual basis as more data is entered. I have broken down each year into quarterly sections. They are follows (using this year as the example) and they are all inclusive dates: Apr 1, 06 - June 30, 06 Jul 1, 06 - Sept 30, 06 Oct 1, 06 - Dec 31, 07 Jan 1 ,07 - Mar 31, 07 So now I need to figure out the dates in the database that are between each of those quarterly sectors (there is also data in the database from previous years). I thought it would be easiest to enter a formula using the COUNTIF and use the crtieria of all dates that were greater then Apr 1, 06 and less then July 1, 06. But that doesn't work as COUNTIF can only have one criteria. As well, I cannot use any sort of COUNTIF "-" COUNTIF since any dates that are entered after June 30 1, 06 will be picked up by the one side of the COUNTIF equation (the greater then April 1, 06) but not the other (the less then June 30, 06). Sorry for making this so long, hope that clarifies it a bit!!! As well, when a formula has "--", what does that mean? Do you actually need to enter that as part of the formula or what do you enter in it's place? Sorry if that is dumb q, just unsure of what it means. Thanks so much!!! Steve |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting occurances
Try something like this:
With a list of dates in A2:A100 Build this table in F1:G6 1-Jan-06 2005Q4 1-Apr-06 2006Q1 1-Jul-06 2006Q2 1-Oct-06 2006Q3 Jan 1 ,07 2006Q4 1-Apr-07 2007Q1 B1: 2006Q2 Put this ARRAY FORMULA* in C1 C1: =SUMPRODUCT(--(LOOKUP(A2:A100,E1:E6,F1:F6)=B1)) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. C1 will return the count of dates that are in Fiscal Period 2006Q2 By changing the Fiscal Period in B1, the formula will return counts for dates within that period. Is that something you can work with? ----------------- Regarding the double-negative (--): SUMPRODUCT adds values, but equations that return TRUE or FALSE are NOT numeric. The generally accepted convention is to use a double minus sign (--) to coerce a type conversion from boolean (true/false) to numeric. The dbl-neg causes Excel to convert TRUE and FALSE to 1 and 0, respectively. You could achieve the same results by multiplying a value by 1, but the dbl-neg indicates to knowledgable users that a "type conversion" is being effected. *********** Regards, Ron XL2002, WinXP "SR89" wrote: Ron Coderre Wrote: For the between scenario, this formula is a bit shorter: B1: =SUMPRODUCT((A1:A100(--"01/01/2003"))*(A1:A100(--("01/01/2004")))) Easiest, though, is to enter the min and max dates in separate cells and reference them... B1: (min date to include) B2: (max date to include) Count of dates between those 2 dates, inclusive: B3: =SUMPRODUCT((A1:A100=B1)*(A1:A100=B2)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: For a list of dates in A1:A100 You have a couple options: These formulas EXCLUDE 01/01/2003.... B1: =SUMPRODUCT((A1:A100DATEVALUE("01/01/2003"))*(A1:A100DATEVALUE("01/01/2004"))) or B1: =COUNTIF(A1:A100,"01/01/2004")-COUNTIF(A1:A100,"=01/01/2003") If you want the count of any dates within 2003.... B1: =SUMPRODUCT(--(YEAR(A1:A100)=2003)) Does that help? *********** Regards, Ron XL2002, WinXP "SR89" wrote: Hi, I can't figure out how to count how many occurances fall between two dates. For example, how many of the dates in a column are Jan 01, 2003 and Jan 01, 2004. COUNTIF will count one occurance only, so I think that I have to use maybe SUMPRODUCT but am now sure how the formula would work. Any ideas? Thanks, -- SR89 Hmm, I must be fairly imcompetent as I can't seem to get these to work so I'll lay out the problem in it's entirety since I probably did not give enough info the first time. I have to find the number of occurances between two dates as mentioned. This database will be getting updated on a continual basis as more data is entered. I have broken down each year into quarterly sections. They are follows (using this year as the example) and they are all inclusive dates: Apr 1, 06 - June 30, 06 Jul 1, 06 - Sept 30, 06 Oct 1, 06 - Dec 31, 07 Jan 1 ,07 - Mar 31, 07 So now I need to figure out the dates in the database that are between each of those quarterly sectors (there is also data in the database from previous years). I thought it would be easiest to enter a formula using the COUNTIF and use the crtieria of all dates that were greater then Apr 1, 06 and less then July 1, 06. But that doesn't work as COUNTIF can only have one criteria. As well, I cannot use any sort of COUNTIF "-" COUNTIF since any dates that are entered after June 30 1, 06 will be picked up by the one side of the COUNTIF equation (the greater then April 1, 06) but not the other (the less then June 30, 06). Sorry for making this so long, hope that clarifies it a bit!!! As well, when a formula has "--", what does that mean? Do you actually need to enter that as part of the formula or what do you enter in it's place? Sorry if that is dumb q, just unsure of what it means. Thanks so much!!! Steve -- SR89 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totalling occurances in a column | Excel Worksheet Functions | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
Counting Date Occurances | Excel Worksheet Functions | |||
Counting multiple occurances of a specific string | Excel Worksheet Functions | |||
Counting... | Excel Worksheet Functions |