3

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?

Jobin
  • 1,281
  • 2
  • 11
  • 14
  • When you highlight the original dataset that you create the pivot from, there should be a small text at the bottom saying add this to a data-model tick the box next to it - then when you assign the values in your pivot you'll be able to choose the distinct count function (it will appear) - it will be the last one in the list – Anna Semjén Jun 05 '19 at 13:22
  • Criteria is plural, the singular is criterion... – Solar Mike Jun 05 '19 at 14:52
  • 1
    @AnnaSemjén This feature is not available in 'Excel for Mac'. – Jobin Jun 06 '19 at 06:49
  • You could use the advanced filter option with unique records only then - depending on which column you need, you copy the dataset to another location without duplicate values and create the pivot table. But this is still cumbersome especially if you want to have and additional filter going forward. – Anna Semjén Jun 06 '19 at 08:45
  • Looks like lots of useful features not available on Mac version - Spreadsheet compare application also missing.. – SystemsInCode Nov 12 '19 at 12:10

3 Answers3

2

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.

Karsten S.
  • 2,349
  • 16
  • 32
  • This works, however there's an error in line 4. Semicolons need to be commas. Working Example:`=IF(MATCH(AM2,AM:AM,0)=ROW(AM2), 1, 0)` – randalv Jun 14 '20 at 15:35
  • I think this is a language "issue". I'm using the German version and Excel wants to have semicolons. In English versions you probably have to use commas. Don't know about other languages - and I agree that this is a very weird behaviour. BTW: I translated the function names to the english ones to be international but forgot about the divider. – Karsten S. Jun 18 '20 at 09:06
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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
user3461952
  • 21
  • 1
0

The non-technical way is to create the pivot table on the column you're interested in with its count. Note the spreadsheet row number corresponding to the top of the pivot table (say A). Go to the bottom of the pivot table. Note the spreadsheet row before the Grand Total row (say B). Subtract B from A.

Or create the pivot table using only the value you want to count distinct for. Press control A to select. Read off the Count in the status bar towards the bottom right. Subtract 2.

Tim Chaffe
  • 1