COUNTIF #VALUE! ERROR USING DEFINED RANGE NAME

Question

Formula is

=COUNTIF(Range1,"COMPLIANT")

Range1 is the defined name of about 15 non contiguous cells on the same page. I read the advice here and it states my range has too many characters.  It will not let me remove the words "Sheet1!" from the Range1 string.

The real formula will have about 80 non contiguous cells involved in the name range. 

I cannot use macros because the people this spreadsheet goes to are macro adverse. 

Can anyone help? Thanks.

Answer 1

Then use the second part of my post - a range of 15 cells with links to the non-contiguous cells.

Or, if there is some other logic that you could use, you could try COUNTIFS, with extra criteria as needed, like

=COUNTIFS(C:C,"COMPLIANT",B:B,"Overall", A:A,"East",F:F,"1st Quarter")

 For help with that, though, you would need to describe the structure and logic of your worksheet.

Answer 2

I cannot do that.  The other cells have the word "COMPLIANT" that are not related to this count, so reason non contiguous.

Answer 3

You may be able to just use a larger single range:

=COUNTIF(A1:Z1000, "COMPLIANT")

Or some other logic.

If that fails, use a small range of 15 cells, where each cell is linked to one of your noncontiguous cells, and use those cells to perform the count:

=COUNTIF(Z1:Z15, "COMPLIANT")