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?

Solution 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.

Solution 2

The following solution has a few steps, but the payoff is that it scales very nicely, because the formula is just as performant on large sets as on small ones, as opposed to lookup/match which get slower the larger the set is.

(1) Custom sort on the fields with the duplicate values. e.g. "Email Address" n.b. If you prefer to count a particular instance (i.e. the record with the latest creation date), set the sort so that those duplicates will appear first.

(2) Create a new column, Call it Unique Count or what have you. In that column use a formula that is 1 if the preceding value and current value are not equal. E.g. =IF(EXACT(A2,A3),0,1)

(3) Fill down.

(4) Pivot on this table. Now when you do Count/Sum, use the Unique Count.