Share via

Using COUNTIF with a defined name for range

Anonymous
2012-12-05T07:20:39+00:00

I have a group of cells, that are not adjacent, with a defined name.  I want to use COUNTIF to look throughout this defined name range and count based on criteria given i.e. a particular number.  If I use the defined name with something like Sum(definedname) it works fine, but when I use COUNTIF(definedname,3), it returns a #VALUE!.  Can a defined name range be utilized in this manor?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2012-12-05T07:54:45+00:00

Hello,

Countif() does not work on non-contiguous ranges, The most common workaround is to use several Countif() on contiguous ranges and add up the results.

cheers, teylyn

Was this answer helpful?

0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-12-05T07:44:46+00:00

    Yes.  I originally tried the COUNTIF and got the error so then I tried something similar ... the SUM formula and it worked so I went back and did the COUNTIF again and got the error.  I think it's because my defined name range is not all together in a continuous row or column i.e.  B2:B7,B12:B17,B25:B35.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2012-12-05T14:21:44+00:00

    Thanks.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-12-05T07:28:57+00:00

    It's working fine for me.  Have you tested both sum and countif functions on the same sheet?

    Was this answer helpful?

    0 comments