Скачать или смотреть How to Count Cells in a Non-Contiguous Named Range in Excel 2019

Discover an effective method to count cells in a non-contiguous Named Range using functions in Excel 2019 without VBA.
---
This video is based on the question https://stackoverflow.com/q/68018023/ asked by the user 'Paul Martin' ( https://stackoverflow.com/u/11713551/ ) and on the answer https://stackoverflow.com/a/68019226/ provided by the user 'Charles Williams' ( https://stackoverflow.com/u/88173/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Excel 2019: how to count cells in a non-contiguous Named Range

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Counting Cells in Non-Contiguous Named Ranges in Excel 2019

If you've ever worked with Named Ranges in Excel, particularly those that are non-contiguous, you may have run into a frustrating limitation. While it's easy to get the total number of cells in a contiguous range, counting cells in a non-contiguous Named Range isn't as straightforward. In this guide, we’ll explore the challenge and provide a workable solution without needing to dive into VBA.

Understanding the Problem

A Named Range that consists of multiple areas (non-contiguous) provides great flexibility for data management. However, when trying to count the cells in such a range using built-in functions like COUNTA, COUNTIF, or COUNTBLANK, Excel throws up a barrier because these functions don’t support non-contiguous ranges directly. Instead, they typically result in errors. This can make the task seem impossible at first glance!

The Solution Overview

Starting with Excel's built-in functions, we can navigate around this issue using a combination of functions. Here’s a step-by-step method to count the cells in a non-contiguous Named Range, assuming we have named our range MultiArea. Below are the key functions we'll utilize:

AREAS() - Determines the number of areas in a Named Range

COUNTA() - Counts the number of non-empty cells in a given range

COUNTBLANK() - Counts all blank cells in a specific range via INDEX

Step-by-Step Breakdown of the Solution

Use AREAS() Function

The AREAS(MultiArea) function will return the total number of separate areas within your MultiArea Named Range. For instance, if your range has three separate areas, this will return 3.

Count Non-Empty Cells with COUNTA()

The COUNTA(MultiArea) function will calculate the total number of non-blank cells across all areas in the MultiArea. This is a straightforward count that you can utilize directly.

Count Blank Cells Using COUNTBLANK() and INDEX()

Since the COUNTBLANK() function cannot handle non-contiguous ranges directly, we can use the INDEX() function to specify each area within the range:

COUNTBLANK(INDEX(MultiArea,,,1)) counts blank cells in the first area.

Likewise, for subsequent areas, use:

COUNTBLANK(INDEX(MultiArea,,,2))

COUNTBLANK(INDEX(MultiArea,,,3))

NOTE: Extend this for as many areas as exist in the MultiArea Named Range.

Final Formula

Putting it all together, the final formula to count all cells (both blank and non-blank) in a non-contiguous Named Range named MultiArea looks like this:

[[See Video to Reveal this Text or Code Snippet]]

Replace the 3 in the formula with the actual number of areas you have in your MultiArea range.

Conclusion

Counting cells in a non-contiguous Named Range in Excel 2019 may initially seem challenging due to the limitations of several common functions. However, by using the combination of AREAS(), COUNTA(), and COUNTBLANK() alongside INDEX(), you can effectively calculate the total number of cells in your Named Range without resorting to VBA.

Feel free to experiment with this approach in your own Excel sheets, and take advantage of Named Ranges more effectively!