Unique count in different format

[Siyanna]

Hi

I have a huge list of data lists data in this format

Name Date Question UN
A 01/01/18 Q1 1
A 01/01/18 Q2 1
A 01/02/18 Q1 2

i have pivoted the table and added a slicer for Date and UN. I’m trying to get a unique count of records not including the questions

so for UN 1, A and 01/01/2018 the count should be 1

can I do this with a pivot table or do I have to add a further column in my source table that has a formula that takes that into account?

if yes, what formul can I have? The idea is to get how many people responded

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(($D$2:$D$4=1)*($A$2:$A$4="A")*($B$2:$B$4=DATE(2018,1,1)),
    MATCH($D$2:$D$4&$A$2:$A$4&$B$2:$B$4,$D$2:$D$4&$A$2:$A$4&$B$2:$B$4,0)),
    ROW($A$2:$A$4)-ROW($A$2)+1),1))

 

[Siyanna]

Thank you

Is it possible to get around it in the pivot table just encase my data has more columns and rows hence why i have slicers

Main thing to see is how many responses a person made so the only the thing different will be the questions