Summary statistics for a golf scores spreadsheet

[Neuman]

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

 

[Biff]

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]

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

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

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

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

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

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

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