CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Number

**Rhall6310** · 01-23-2018, 03:20 PM

Working with 2 linked spreadsheets
Sheet 1 contains a range of 20 cells each containing golf scores from last 20 rounds of a single golfer.
Sheet 2 contains a cell that represents the golfers "Target Score For Shooting Handicap" (ie 88.30 which is based on a combination of the golfers handicap index and the course rating) this number is in a cell for reference in the CountIF formula.

I am trying (unsuccessfully) to calculate the number of times an individual golfer had scores (from the range of 20 rounds) that was within +3 or -3 of the "Target Score For Shooting Handicap" (ie 88.30).

The formula Im using keeps resulting in a "0" even thought there are several rounds in the range of 20 that are within the criteria.

Any insight is appreciated. Thx Bob

**Rhall6310** · 01-23-2018, 03:23 PM

This is the formula I am using that is not getting me the results desired.
=COUNTIF('[hdcpxl604.xlsm]Scoring Reports'!$L$5:$L$24,"+-3k6")

**ben_hensel** · 01-23-2018, 03:29 PM

"+-" doesn't mean anything to Excel.

what you want is something more like:

Please Login or Register  to view this content.

This will count all the rows that are more than/equal to K6 -3 and less than/equal to K6 +3; you count the upper and lower bands as separate criteria.

**Rhall6310** · 01-24-2018, 02:16 AM

Ben,
Thanks for the formula - it worked perfectly for the results I was after - However I have had to change my data source to simplify and maintain consistency.
1. My criteria has changed - I only need to match the numerical value of cell H6 exactly and if within -3 points. I do not need the +3 points for the formula.
2. The Scores worksheet is in the same workbook with cells B2:U2 - each with the specific number posted from each of the last 20 rounds of golf that are to be counted if it is equal to or -3points to the Target number in cell H6..

The formula Im using is =COUNTIF(Scores!B2:U2, "<="& H6 -3) - I have adapted this from the formula you provided earlier.

The results I get is 0 even though the cells of B2 - U2 contains several golf scores that qualify to be counted being either an exact match or within -3points lower.

Cannot figure out why this is not working. Thx Bob
I wish I knew how to attach my worksheet.

**ben_hensel** · 01-24-2018, 01:16 PM

Originally Posted by Rhall6310

1. My criteria has changed - I only need to match the numerical value of cell H6 exactly and if within -3 points. I do not need the +3 points for the formula.
2. The Scores worksheet is in the same workbook with cells B2:U2 - each with the specific number posted from each of the last 20 rounds of golf that are to be counted if it is equal to or -3points to the Target number in cell H6..

The formula Im using is =COUNTIF(Scores!B2:U2, "<="& H6 -3) - I have adapted this from the formula you provided earlier.

It should be ">=" (greater than or equal to) not "<=" (less than or equal to); the COUNTIF you have there will return only values that are less than three below target.

You still need to use COUNTIFS with two terms; it's just that the second one, rather than being H6+3, will be H6+0 (or just written H6).

Because with just one term, even fixing the greater/less than thing, the COUNTIF you've got there would return all values more than H6-3; for example, if I came along and did a round with my truly terrible driving (my putting is merely bad), I would score something stupid like 200, but I would still be counted because 200 >= 88 -3

Leaving aside all that though, if you've still got other problems with it not recognizing values, I would expect it's not assessing numbers correctly or something obnoxious like that.

I wish I knew how to attach my worksheet.

Either click the [Reply] Button on the bottom right corner of a post, or the [Go Advanced] button below the Quick Reply dialogue box on the far right.

That will bring you to the "New Reply" tab, and beneath the dialogue box on that page, there are "Additional Options"; the second one down is "Attachments"; click the "Manage Attachments" to launch the attachment wizard, then you can either click "Browse" in the first box to load from your computer, or post a URL in the second box. Click the "Upload" button under the correct box after you've done that, and then "Close this window"; that should attach your file to the post.

**Rhall6310** · 01-25-2018, 10:53 AM

Ben
I really appreciate your help however still have an issue getting the Countif or Countis formula to work properly.
It may be something as you mentioned:
"Leaving aside all that though, if you've still got other problems with it not recognizing values, I would expect it's not assessing numbers correctly or something obnoxious like that."

Attachment:
I have linked the data set to the same worksheet as the Golf Handicap Report and still cannot get a carrect answer.

I am trying to accomplish what seems pretty simple and straightforward - Have been using the CountIf and CountIfs formulas but it is one error after another.

1. The statistic I am trying to calculate is (COLUMN I) "The Number of Rounds Played to Handicap or Better"

2. This is the number of rounds a player equaled or played a lower score than his calculated (COLUMN H) "Target Score Play to HDCP"

3. The information base I am using is the "Past 20 Rounds" Rows S-AL of the Golfers Report on the worksheet.

4. The objective is to display how many golf rounds of the Past 20 Rounds was the "Golfers Posted Score" equal to or lower than the "Target Score" in COLUMN H. If your target score is 88 the calculation should return the number of rounds from the last 20 that are equal to or lower.
For Example, Ken Bergs Target Handicap Score is 88 - and he has scored 8 rounds in the last 20 of 88 or lower - His # of Rounds Played to HDCP should read "8"

Again I have attached the worksheet with my Handicap Report and Added the sample 20 rounds for several of us to test against.

I cannot get this to work - Any suggestions or help is appreciated.

Thx Again, Bob

**ben_hensel** · 01-25-2018, 05:36 PM

Yeah the data-pull in P6:AI12 is using the LEFT function -- that means that whatever it pulls is actually a text string, not a number.

There are two ways to do it:
1) Pull with a different function, such as =ROUND(path, 0) or =INT(path) or so.
2) Force the text to be assessed as a number, for example, by sticking a plus zero on the end, so it's "=LEFT(path, 2)+0" in the cell.

Hopefully that will fix your problem.

**Rhall6310** · 01-26-2018, 10:55 AM

Ben
Wonderful - really appreciate your help!
Bob

CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Number

LinkBack

Thread Tools

Rate This Thread

Display

CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Number

Re: CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Nu

Re: CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Nu

Re: CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Nu

Re: CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Nu

Re: CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Nu

Re: CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Nu

Re: CountIF Function on Range of Cells Meeting a +-3 points of Designated Nuemeric Cell Nu

Thread Information

Users Browsing this Thread

Similar Threads

COUNTIF formula only after meeting another cell's not null criteria

Copy a range of cells to the right of a cell in a colum meeting a condition

specifying the Countif range meeting a condition

[SOLVED] I need help creating a list using a number in a designated cell as my range.

Designated a cell as a name range

Counting the Number of Values Meeting Criteria, but Only for Every 3rd Cell in a Range

In COUNTIF function, how do I count the number of cells which con.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions