How to use CUBESETCOUNT function in Excel?

The `CUBESETCOUNT` function in Excel is used to count the number of items in a set returned by a CUBESET function. This is particularly useful when working with OLAP (Online Analytical Processing) data sources in connection with PowerPivot or other data models in Excel.

Syntax

CUBESETCOUNT(set)

set: This is the CUBESET function or a reference to a cell containing the CUBESET formula from which you want to count the number of items.

Example Usage

Suppose you have a data model or OLAP cube where you’ve created a set using the `CUBESET` function, and you want to count how many items are in that set. Here’s a simple example:

   =CUBESET("SalesCube", "[Product].[Product Categories].Children", "Product Categories Set")

Assume cell `A1` contains the following CUBESET function:

This function retrieves a set of all product categories from a cube named “SalesCube”.

   =CUBESETCOUNT(A1)

To count the number of items in this set, you would use the `CUBESETCOUNT` function in another cell (e.g., `A2`):

This will return the number of product categories within the set created by the `CUBESET` function in `A1`.

Notes

Ensure that the data connection to the OLAP cube or PowerPivot model is active.
If the CUBESET function you are referencing results in an error, the CUBESETCOUNT will also return an error, typically `#VALUE!`.
The `CUBESETCOUNT` function is available in Excel 2007 and later, but it requires the use of a data connection to a source that supports the MDX (Multidimensional Expressions) query language, such as SQL Server Analysis Services.

By using `CUBESETCOUNT`, you can efficiently summarize the number of items in complex data models or cubes without manually extracting and reviewing the items.

facebooklinkedin