SOLVED

# Use of Named Ranges in Countif formulas

Copper Contributor

# Use of Named Ranges in Countif formulas

It is my understanding that we can't use a named range that consists of non-contiguous cells in a countif formula. eg: COUNTIF(named_range,C280). Is that correct?

If this is correct, it makes me wonder what the point of named ranges is. It seems like it should be used to make challenging ranges easier to maintain, but it's pointless if it can't be used this way.

4 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Use of Named Ranges in Countif formulas

You are correct, but that is not a limitation of named ranges, but of the COUNTIF function (and related functions). Other functions, such as SUM and COUNTA, work fine with named ranges consisting of non-contiguous cells.

# Re: Use of Named Ranges in Countif formulas

Good to know, but still disappointing. Thanks for responding.

# Re: Use of Named Ranges in Countif formulas

If you know the name of your cell range, you can manually enter it in the countif function. You will just not be able to see the range names as a drop-down list in the name box.

For example this works: =Countif(throws,6) - the range named throws contain randomly generated numbers between 1 and 6.  The countif function determines how many times the number 6 was thrown. You just have to determine the name of your range up front.

# Re: Use of Named Ranges in Countif formulas

Sure, you can use a named range in COUNTIF, but the problem of the OP was that "we can't use a named range that consists of non-contiguous cells in a countif formula". COUNTIF only works with contiguous ranges, named or not.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Use of Named Ranges in Countif formulas

You are correct, but that is not a limitation of named ranges, but of the COUNTIF function (and related functions). Other functions, such as SUM and COUNTA, work fine with named ranges consisting of non-contiguous cells.