Counting Non Blanks and Non Zero's

How do I count the number of cells in a row that are either not blank or above zero in a non sequential range?

For example, I want a column in a golf course chart to count the applicable "Par" columns of each of the course holes. Although my sheet has provisions for 18 holes across the page, some courses are only 9 hole courses, so the remaining holes 10-18 are blank in their Par columns.

In my course summary table, I want a column to be "Number of Holes" which is a calculation of how many Par cells have data in them.

The par for each course is within the following cells:
($C3,$F3,$I3,$L3,$O3,$R3,$U3,$X3,$AA3,$AD3,$AG3,$AJ3,$AM3,$AP3,$AS3,$AV3,$AY3,$BB3)
Pic to help explain:
Excel_Count_Non_Blanks.jpg

Thanks for the quick solution, but alas, there are and will be more cases of smaller "pitch n putt" courses which could have anywhere from 2 holes on up...

Try

=INT(LOOKUP(99,3:3,COLUMN(3:3))/3)

Assuming none of the numbers will be larger than 99.

Well, after a closer look...looks like numbers can be larger than 99

Then
=INT(LOOKUP(9.99999999999999E+307,3:3,COLUMN(3:3))/3)

Thanks guys, although I liked the look of Jonmo1's solution, it didn't work quite right, and I didn't understand it enough to be able to adjust it. It sure seems interesting though. I utilized Harribone's solution and it works good, albeit like mentioned, not an elegant solution, but no one will see it anyway, Thanks very much Harribone.

Is it possible to turn that into a SUMPRODUCT formula somehow, or does the non-sequential range screw that up too?

In what way did it not work?

Can you give example of situtation that it produced the wrong result?
What result DID it return, and what result were you expecting and why?

Can you post an actual book instead of pictures?

It seemed to post a result of either 0 or 20 when I was expecting either 0,9,18, or other, not 20 though... Maybe due to my error in copy-pasteing, not sure, but see attached,

courseHOLECOUNTformula.xlsx

Ah, circular references.

Try this in BE3 and filled down.
=INT(LOOKUP(9.99999999999999E+307,A3:BD3,COLUMN(A3:BD3))/3)

It will return #N/A if the whole row is blank.
But that can be corrected if you need it.
What result would you like it to show in that case?

Yes that does work better; instead of #N/A on blank rows (blank rows mean I have a course without enough hole data), can the cell say "Update Required" or something?

Also, and more importantly, can you briefly explain what your formula is doing so I can learn from that approach?

Sure,

The 9.999999.... is basically just a VERY Large number. The largest number allowed in a cell.
Thus ensuring it is larger than any number in the range.
The lookup then finds the last number entry (furthest to the right)
And returns the corresponding Column #.

It then devides that number by 3 (you have groups of 3 cells per hole).
And then INT basically rounds down the result of that devision to a whole number.


Since you're file was an xlsx estention, I assume you have xl2007 or higher.
So to make it say "Update required" instead of #N/A, we can use IFERROR

=IFERROR(INT(LOOKUP(9.99999999999999E+307,A3:BD3,COLUMN(A3:BD3))/3),"Update Required")

Hope that helps.

Perfect. Thank you very much for the solution, and the explanation,