How do you perform the COUNTIF function with a range that is not linear?

[dnnr]

Here is the failing function with the explanation below:

=IF(AG4>0, "Failed", IF(COUNTIF(Compliance,"P")=0,"N/A",IF(COUNTIF(Compliance, "L")>0, "Partial Pass", "Passed")))

I have a spreadsheet where I record the pass/fail status of criteria for projects. These criteria are all contained in sequential columns. The result in the final column is whether or not the project passed, failed, passed in part, or is not applicable to a pass/fail status. The overall pass/fail status works just fine.

Some of these criteria are managed by different people, and I'd like to pull data from the original columns as to how they are performing specifically. I could just rearrange the criteria to be sequential so I could use a COUNTIF with a range, but I ALSO want to pass/fail other criteria based on each stage of the project.

So in a nutshell: I want to perform a COUNTIF on unique cells that do not occur in a linear, sequential range. What I've tried to do so far was name these cells "Compliance" and then use the name as the range within the COUNTIF function, but it returns a "#VALUE" error.

Any advice?

[NBVC]

Try replacing each of your Countif() functions with something like:

Please Login or Register  to view this content.

where you place your ranges between quotations and separated by commas.