Distinct Count Workaround

[Greeey]

Hi,

I am dealing with a large data set and forced to use Excel for Mac v 16.49, so I don't have access to PowerPivot.

I am setting up a file with a number of slicers on this data, and want to display 2 pieces of data:
- a sum of one field - CHECK
- a distinct count of another field - This is where I'm hitting an issue

With a recent version of Excel for Windows, I would just be able to use the inbuilt Distinct Count function. As I don't have this, I'm trying to think of a workaround.


I could do a formula workaround easily, but that only works If I was looking at the whole data set, or using manual filters. With slicers, I don't think I can do a Formula workaround on the count, unless I can find a way to flag in the data set whether each row is included in the current slicer selections and then

Can anyone tell me if there's an easy way to do that?

Alternatively, I think if I have another working pivot which will show the selections from the slicers, I could have another set of cells point to that pivot table and use those...but it's not going to work for multiple selections.

I think I've explained that very badly, but would appreciate any thoughts as I've driven myself insane today!

[alansidman]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.