Counts and Totals in Excel 3

[Jetter88]

Hi...
i'm trying to do some calculations based on a range and comparing 2 different numbers. Based on a golf scorecard format.

A player enter his/her score per hole. At the end of the spreadsheet there will be a section that totals up automatically the birdies, pars, bogeys, etc. That is the part that is not working.

Each cell need to compare the score entered per hole to the hole par. for example: player enters 4 for hole 1 and the par is 3. The column at the end of the sheet for BOGEYS will count 1 for that row of scores.

what would the function be? I tried COUNTIF... but can't figure out the criteria without it failing.

thanks in advance!
Mark

 

[sawedoff]

Put in a column that compares them and puts in a 1 if it is a birdie, a 2 if is a bogey, or whatever code you want. Then do a countif based on the contents of that column.

Sawedoff

 

[randy700]

Assuming....
B3:J3 are the actual scores (holes 1-9).
B7:J7 are the par scores (holes 1-9).

L3 =IF(B3=B7-1,1,0)+IF(C3=C7-1,1,0)+IF(D3=D7-1,1,0) etc



Randy

 

[firefytr]

Set your table structure up much like an Access database. Have a column for Par, one for Shots. From there it is a simple minus calculation to figure out if they had a birdie or a bogey.

To count all birdies/bogeys, use a CountIf function then on the resultant calculation (assumed to be on every record/row of your table structure)..

Count everything less than zero:
=COUNTIF(C:C,"<0")

Count everything greater than zero:
=COUNTIF(C:C,">0")

Note that the range C:C should be changed out for your range that has the minus calculation in it. Please post back with more details if you'd like further assistance.

-----------
Regards,
Zack Barresse

 

[Jetter88]

Thanks to all for the quick responses!!

I've used all of the ideas to see if they work and they do! I was trying to avoid creating a new column to calculate the birdies, pars,etc and then counting those up. I ideally wanted to get it all into one calculation.
The closest was Randy700, but there is lots of typing for that, but it works great. I did use the other ideas too... but they require extra columns (which can be hidden).

In the end... all suggestions got me a result...

thanks