SheetSkill

CUBESETCOUNT function

Counts how many members a CUBESET contains.

Works in desktop Excel only

Syntax

=CUBESETCOUNT(set)

How to use CUBESETCOUNT in Excel, step by step

CUBESETCOUNT doesn’t run in a web practice sheet because it reads a data model (Power Pivot / OLAP) connection. Follow these steps in Excel itself.

You’ll need

  • A cell with a CUBESET formula
  1. Put your data in a table. Click inside your data, press Ctrl+T, tick “My table has headers” and click OK. Give it a clear name on the Table Design tab (for example Sales).
  2. Add the table to the Data Model. Go to Insert › PivotTable › From Table/Range, tick “Add this data to the Data Model” and click OK. Excel now has a connection called ThisWorkbookDataModel.
  3. Build a quick PivotTable first. Drag a field (for example Region) to Rows and a number (for example Amount) to Values. This creates the measure the CUBE functions will read, such as [Measures].[Sum of Amount].
  4. Let Excel write the formulas for you. Click the PivotTable, then PivotTable Analyze › OLAP Tools › Convert to Formulas. Every cell becomes a CUBEMEMBER or CUBEVALUE formula you can copy and edit.
  5. Create a CUBESET. In B1: =CUBESET("ThisWorkbookDataModel","[Sales].[Product].Children","Products").
  6. Count it. In B2: =CUBESETCOUNT(B1). Use it to know how many CUBERANKEDMEMBER rows you need.

Example

=CUBESETCOUNT(B1)

Result: 24

The argument must be a CUBESET (or a cell containing one).