SOLVED

Use of Named Ranges in Countif formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2594418%22%20slang%3D%22en-US%22%3EUse%20of%20Named%20Ranges%20in%20Countif%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594418%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20is%20my%20understanding%20that%20we%20can't%20use%20a%20named%20range%20that%20consists%20of%20non-contiguous%20cells%20in%20a%20countif%20formula.%20eg%3A%20COUNTIF(named_range%2CC280).%20Is%20that%20correct%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20is%20correct%2C%20it%20makes%20me%20wonder%20what%20the%20point%20of%20named%20ranges%20is.%20It%20seems%20like%20it%20should%20be%20used%20to%20make%20challenging%20ranges%20easier%20to%20maintain%2C%20but%20it's%20pointless%20if%20it%20can't%20be%20used%20this%20way.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2594418%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594506%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20of%20Named%20Ranges%20in%20Countif%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594506%22%20slang%3D%22en-US%22%3EGood%20to%20know%2C%20but%20still%20disappointing.%20Thanks%20for%20responding.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594483%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20of%20Named%20Ranges%20in%20Countif%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594483%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1114876%22%20target%3D%22_blank%22%3E%40NancyBogg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20correct%2C%20but%20that%20is%20not%20a%20limitation%20of%20named%20ranges%2C%20but%20of%20the%20COUNTIF%20function%20(and%20related%20functions).%20Other%20functions%2C%20such%20as%20SUM%20and%20COUNTA%2C%20work%20fine%20with%20named%20ranges%20consisting%20of%20non-contiguous%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@NancyBogg 

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.

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