COUNTIF function using commas

• In my coursework I need to use the COUNTIF function. I have two worksheets; one is on the results of a sportsday; the others is a medals sheet. The problem is as follows:
I use this formula:
=COUNTIF('INPUT SCREEN'!C6,'INPUT SCREEN'!C13,'INPUT SCREEN'!C20,'INPUT SCREEN'!C27...and so on...till...'INPUT SCREEN'!C125,"1")
The INPUT SCREEN is the name of the results worksheet, and the "1" is comes up in the ranking
bar and means 1st place and therefore gold medal. I put in the commas, so that EXCEL only counts the "1"s in the ranking bars, and not also in the point bars.
Th problem that came up, was that EXCEL always marked the third "object" in the formula, in this case, C20 telling me that something was not right with the formula.
Is there any way to solve this problem; or even another option in counting only the "1"s in the rank bars
:question:

• Well, if what you call a "ranking bar" is a cell, then could you just count up the instances of "1"s on the Input Screen sheet, from range C6:C125? If so, this formula might do the job:

=COUNTIF('Input Screen'!C6:C125,"1")

But if there's something more to your workbook than meets the eye, please repost.

Tom Urtis

• CAN I GET COUNT IF ONLY VISIBLE CELLS

• Quote

Originally posted by s_u_resh
CAN I GET COUNT IF ONLY VISIBLE CELLS

using formulae, you can COUNT on visible cells using the =SUBTOTAL( function

to add a criteria you may need to add further IFs in seperate cells, or "resort" to VBA to achieve a COUNTIF simulator....

At work, I want a SUMIF on visible cells only, but have not been able to achieve this just via a single formulae :no:

hope this helps, albeit negatively

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!