I have stumble upon the only way to calculate the number of unique items in Excel and I am taking notes here
Add a column to the database and then add that field to the pivot table.
For instance if we want to count the unique occurrences of URL/Keyword, we shall add in the raw data:
– the row URL/Keyword in the raw data.
– In the first data row, enter a formula that refers to the URL and Kws columns. For example:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)
Copy the formula down to all rows in the database.
Then, add the field to the data area of the Excel pivot table.
source: Pivot technics