counting occurances

[SR89]

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,

 

[Dave]

SR89

=SUMPRODUCT(--(A1:A20>B1),--(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

 

[Ron Coderre]

For a list of dates in A1:A100

You have a couple options:

These formulas EXCLUDE 01/01/2003....
B1:
=SUMPRODUCT((A1:A100>DATEVALUE("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

 

[Marcelo]

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:

 

[Ron Coderre]

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

 

[SR89]

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 cell
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


:

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


:


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,

Hmm, I must be fairly imcompetent as I can't seem to get these to wor
so I'll lay out the problem in it's entirety since I probably did no
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 dat
is entered. I have broken down each year into quarterly sections. The
are follows (using this year as the example) and they are all inclusiv
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 betwee
each of those quarterly sectors (there is also data in the databas
from previous years).

I thought it would be easiest to enter a formula using the COUNTIF an
use the crtieria of all dates that were greater then Apr 1, 06 and les
then July 1, 06. But that doesn't work as COUNTIF can only have on
criteria. As well, I cannot use any sort of COUNTIF "-" COUNTIF sinc
any dates that are entered after June 30 1, 06 will be picked up by th
one side of the COUNTIF equation (the greater then April 1, 06) but no
the other (the less then June 30, 06).

Sorry for making this so long, hope that clarifies it a bit!!! A
well, when a formula has "--", what does that mean? Do you actuall
need to enter that as part of the formula or what do you enter in it'
place? Sorry if that is dumb q, just unsure of what it means.

Thanks so much!!!

Stev

 

[Ron Coderre]

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