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,


--
SR89

[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

"SR89" <[email protected]> wrote in message
news:[email protected]...
>
> 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

[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


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

[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:

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

[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


"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: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
>
>
> "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
> >

[SR89]

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

[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


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