Summary statistics for a golf scores spreadsheet

The 2 columns I'm concerned about are:
Column 1 has years (2000-2006)
Column 2 has numbers (70-90) which are my golf scores
Does anyone know how I can run summary statistics on the scores based on the
years of the first column?
Some examples:
Ex 1- count number of times in 2002 that the score was between 70-74
Ex 2- find the maxmium golf score in 2005
Ex 3- count number of times in 2004 that the score was > 80
I know how to use the sumif and countif functions and array formulas, but
haven't been able to get this figured out.
Thank you so much for your help!!
David

Hi!

>Ex 1- count number of times in 2002 that the score was between 70-74
>[inclusive?]

=SUMPRODUCT(--(A1:A100=2002),--(B1:B100>=70),--(B1:B100<=74))

>Ex 2- find the maxmium golf score in 2005

Entered as an array using the key comination of CTRL,SHIFT,ENTER:

=MAX(IF(A1:A100=2005,B1:B100))

>Ex 3- count number of times in 2004 that the score was > 80

=SUMPRODUCT(--(A1:A100=2004),--(B1:B100>80))

It's better to use cells to hold the criteria and then just refer to those
cells. This gives you much more versatility.

C1 = 2004
D1 = 80

=SUMPRODUCT(--(A1:A100=C1),--(B1:B100>D1))

Biff

"Neuman" <Neuman@discussions.microsoft.com> wrote in message
news:CECF7455-B9DC-4C24-AA4C-4CDA794ACC50@microsoft.com...
> The 2 columns I'm concerned about are:
> Column 1 has years (2000-2006)
> Column 2 has numbers (70-90) which are my golf scores
> Does anyone know how I can run summary statistics on the scores based on
> the
> years of the first column?
> Some examples:
> Ex 1- count number of times in 2002 that the score was between 70-74
> Ex 2- find the maxmium golf score in 2005
> Ex 3- count number of times in 2004 that the score was > 80
> I know how to use the sumif and countif functions and array formulas, but
> haven't been able to get this figured out.
> Thank you so much for your help!!
> David

Thanks Biff- that's great! I really appreciate it.

"Biff" wrote:

> Hi!
>
> >Ex 1- count number of times in 2002 that the score was between 70-74
> >[inclusive?]
>
> =SUMPRODUCT(--(A1:A100=2002),--(B1:B100>=70),--(B1:B100<=74))
>
> >Ex 2- find the maxmium golf score in 2005
>
> Entered as an array using the key comination of CTRL,SHIFT,ENTER:
>
> =MAX(IF(A1:A100=2005,B1:B100))
>
> >Ex 3- count number of times in 2004 that the score was > 80
>
> =SUMPRODUCT(--(A1:A100=2004),--(B1:B100>80))
>
> It's better to use cells to hold the criteria and then just refer to those
> cells. This gives you much more versatility.
>
> C1 = 2004
> D1 = 80
>
> =SUMPRODUCT(--(A1:A100=C1),--(B1:B100>D1))
>
> Biff
>
> "Neuman" <Neuman@discussions.microsoft.com> wrote in message
> news:CECF7455-B9DC-4C24-AA4C-4CDA794ACC50@microsoft.com...
> > The 2 columns I'm concerned about are:
> > Column 1 has years (2000-2006)
> > Column 2 has numbers (70-90) which are my golf scores
> > Does anyone know how I can run summary statistics on the scores based on
> > the
> > years of the first column?
> > Some examples:
> > Ex 1- count number of times in 2002 that the score was between 70-74
> > Ex 2- find the maxmium golf score in 2005
> > Ex 3- count number of times in 2004 that the score was > 80
> > I know how to use the sumif and countif functions and array formulas, but
> > haven't been able to get this figured out.
> > Thank you so much for your help!!
> > David
>
>
>

You're welcome. Thanks for the feedback!

Biff

"Neuman" <Neuman@discussions.microsoft.com> wrote in message
news:C0C654A4-A0AB-414C-802A-3E67C6DA2190@microsoft.com...
> Thanks Biff- that's great! I really appreciate it.
>
> "Biff" wrote:
>
>> Hi!
>>
>> >Ex 1- count number of times in 2002 that the score was between 70-74
>> >[inclusive?]
>>
>> =SUMPRODUCT(--(A1:A100=2002),--(B1:B100>=70),--(B1:B100<=74))
>>
>> >Ex 2- find the maxmium golf score in 2005
>>
>> Entered as an array using the key comination of CTRL,SHIFT,ENTER:
>>
>> =MAX(IF(A1:A100=2005,B1:B100))
>>
>> >Ex 3- count number of times in 2004 that the score was > 80
>>
>> =SUMPRODUCT(--(A1:A100=2004),--(B1:B100>80))
>>
>> It's better to use cells to hold the criteria and then just refer to
>> those
>> cells. This gives you much more versatility.
>>
>> C1 = 2004
>> D1 = 80
>>
>> =SUMPRODUCT(--(A1:A100=C1),--(B1:B100>D1))
>>
>> Biff
>>
>> "Neuman" <Neuman@discussions.microsoft.com> wrote in message
>> news:CECF7455-B9DC-4C24-AA4C-4CDA794ACC50@microsoft.com...
>> > The 2 columns I'm concerned about are:
>> > Column 1 has years (2000-2006)
>> > Column 2 has numbers (70-90) which are my golf scores
>> > Does anyone know how I can run summary statistics on the scores based
>> > on
>> > the
>> > years of the first column?
>> > Some examples:
>> > Ex 1- count number of times in 2002 that the score was between 70-74
>> > Ex 2- find the maxmium golf score in 2005
>> > Ex 3- count number of times in 2004 that the score was > 80
>> > I know how to use the sumif and countif functions and array formulas,
>> > but
>> > haven't been able to get this figured out.
>> > Thank you so much for your help!!
>> > David
>>
>>
>>