1. Visiting from the US? Why not try DIYnot.US instead? Click here to continue to DIYnot.US.
    Dismiss Notice

Any clever Excel users here?

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

  1. WabbitPoo

    WabbitPoo

    Joined:
    26 Feb 2005
    Messages:
    2,704
    Thanks Received:
    137
    Location:
    Gwynedd
    Country:
    United Kingdom
    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

    Nozzle

    Joined:
    23 Dec 2012
    Messages:
    1,993
    Thanks Received:
    250
    Location:
    Suffolk
    Country:
    United Kingdom
    Can you calculate the difference between your score and the par. When the difference is 0, job done.

    Nozzle
     
  3. eta

    eta

    Joined:
    24 Apr 2008
    Messages:
    347
    Thanks Received:
    23
    Location:
    West Sussex
    Country:
    United Kingdom
    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

    {=SUM(N(headerange=columnname)*N(datarange>0))}
    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 Like x 1
  4. Mottie

    Mottie

    Joined:
    27 Feb 2017
    Messages:
    5,445
    Thanks Received:
    644
    Location:
    Essexshire
    Country:
    United Kingdom
    Maybe it’s me but...

     
  5. WabbitPoo

    WabbitPoo

    Joined:
    26 Feb 2005
    Messages:
    2,704
    Thanks Received:
    137
    Location:
    Gwynedd
    Country:
    United Kingdom

    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
     
  6. Sponsored Links
  7. eta

    eta

    Joined:
    24 Apr 2008
    Messages:
    347
    Thanks Received:
    23
    Location:
    West Sussex
    Country:
    United Kingdom
    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 Thanks x 1
  8. fixitflav

    fixitflav

    Joined:
    20 Mar 2018
    Messages:
    686
    Thanks Received:
    56
    Location:
    Stoke-on-Trent
    Country:
    United Kingdom
    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

    WabbitPoo

    Joined:
    26 Feb 2005
    Messages:
    2,704
    Thanks Received:
    137
    Location:
    Gwynedd
    Country:
    United Kingdom
    got it working thanks
     
  10. eta

    eta

    Joined:
    24 Apr 2008
    Messages:
    347
    Thanks Received:
    23
    Location:
    West Sussex
    Country:
    United Kingdom
    you are welcome
     
Sponsored Links
Loading...

Share This Page