How to get distinct count within pivot table(Excel for Mac) having filters?

Question

Excel for Mac doesn't support Power Pivot and thereby doesn't have distinct count feature.

What is the best workaround to get distinct count in such cases?

Sample Excel Columns:

Period Criteria1 Criteria2 Criteria3 Data

Sample Pivot table:

• Different values in 'Period' will be pivot columns.
• 'Criteria1' can be a filter to pivot table.
• Both 'Criteria2'&'Criteria3' columns can be pivot rows.

Now, count of 'Data' can be obtained directly through pivot.

How to obtain distinct count of 'Data' ?

Answer Options

1. Using 'Countif' on raw data - Cons: Very slow on large data.
2. Counting unique keys made by concatenating Criteria columns - Cons: Gets complex and takes more effort in large data with many criteria columns

Is there any better workarounds to obtain distinct count within pivot table(Excel for Mac) having filters/multiple criteria's?

Answer 1

I think I had a comparable problem and here's how I "fixed" it.

1. Add a column to the table named "DistinctValue" - or "Crit2:Crit3" doesn't matter
2. Add a formula, concatenating the values from all fields you have as a row in your pivot table: =[@Criteria2]&":"&[@Criteria3] - depends a little bit on your values, but for me : as concatenator works fine. Space or no character may also work.
3. Add a column "Distinct"
4. Add a formula: =IF(MATCH([@DistinctValue],Y:Y,0)=ROW([@DistinctValue]), 1, 0)
5. Use this row in the pivot as a value with SUM and name it "Count".

This gives you a 1 for every first row of distinct values in your data table which is used in your Pivot. The data rows used for the pivot table should have exactly one row with a 1 for each section of rows. If you sum it up, you exactly get the distinct count.

If you add a new row to the pivot, you need to add it to the formula in 2. to get distinct values again.

Edit: You probably have to exchange , with ; for other languages in the formulas when also translating the formula names to German for instance.