# Any clever Excel users here?

Discussion in 'Software' started by WabbitPoo, 21 Mar 2020.

1. ### WabbitPoo

Joined:
26 Feb 2005
Messages:
2,710
138
Location:
Gwynedd
Country:
Simple task: I have a spreadsheet for my golf scores. Along the top I have the par for the hole, then a row for each round. I wish to count the number of par scores on each round.

So I thought I could use COUNTIF(range, criteria) but the criteria only seems to allow me to compare to a specific value or one particular cell.

Ideas?

2. ### Nozzle

Joined:
23 Dec 2012
Messages:
2,053
259
Location:
Suffolk
Country:
Can you calculate the difference between your score and the par. When the difference is 0, job done.

Nozzle

3. ### eta

Joined:
24 Apr 2008
Messages:
391
30
Location:
West Sussex
Country:
i'm reasonable good at excel and often answer questions on the excels forums, which maybe better place to go
say -
https://www.excelforum.com/
where you can easily load a sample

ANY way
COUNTIF should work
Assuming the PAR is in ROW 1 - and starts at B2 to S2 - although you may have a column for 1st half after 9 holes

use control+shift+ enter to get the { brackets
if you Rows go up to row 10
And the PAR are in row 1
then
=SUM(N(B1:S1=B2:S10)*N(B2:S10>0))}
Dont need the N function
=SUM((B2:T2=B4:T20)*(B4:T20>0))

i'll try a sample sheet for you
Cant load a XL file, not sure if zip allowed
https://www.dropbox.com/s/a9r6vgoupjoh66p/GolfPar.xlsx?dl=0

Last edited: 21 Mar 2020
• Like x 1
4. ### Mottie

Joined:
27 Feb 2017
Messages:
6,552
799
Location:
Essexshire
Country:
Maybe itâ€™s me but...

5. ### WabbitPoo

Joined:
26 Feb 2005
Messages:
2,710
138
Location:
Gwynedd
Country:

THANKS. I tried this but for some reason I get an error. I'll take some time later to take a look. I'm not clear on

a) the meaning of the curly brackets
b) what *(B4:T20>0) does

thanks

7. ### eta

Joined:
24 Apr 2008
Messages:
391
30
Location:
West Sussex
Country:
the curly brackets are to make it into an array formula - otherwise you get an error
only counting above a zero blank value
you need to
use control+shift+ enter when in the formula bar - that adds the { brackets

• Thanks x 1
8. ### fixitflav

Joined:
20 Mar 2018
Messages:
691
57
Location:
Stoke-on-Trent
Country:
Following Nozzle's suggestion, do that , then use countif, with condition 0. That's assuming you want to count number of par scores, not par or lower.

9. ### WabbitPoo

Joined:
26 Feb 2005
Messages:
2,710
138
Location:
Gwynedd
Country:
got it working thanks

10. ### eta

Joined:
24 Apr 2008
Messages:
391
30
Location:
West Sussex
Country:
you are welcome

1. ### Any Excel and OneDrive experts out there?

WabbitPoo, in forum: Software
Replies:
5
Views:
574
2. ### Excel 2013

tony1851, in forum: Software
Replies:
2
Views:
438

Replies:
4
Views:
468
4. ### Excel column headings

JohnD, in forum: Software
Replies:
3
Views:
477
5. ### Microsoft Excel

jaguar1, in forum: Software
Replies:
3
Views:
481
6. ### Excel - How to group worksheets?

str, in forum: Software
Replies:
1
Views:
387