Excel Count unique value multiple columns

0
votes

I have a workbook with multiple sheets. On sheet1 I would like to count the number of times person in column A has a particular entry in Column C that meets criteria of column D = Content but only count if column B is a unique value.

Using the below formula I can do everything except only counting unique numbers in Column B. I enter this formula in Sheet2 C2 then pull across to I2 then pull down to 6 in each column.

=IF(COUNTIFS(Sheet1!$A$2:$A$150,Sheet2!$B2,Sheet1!$C$2:$C$150,Sheet2!C$1,Sheet1!$D$2:$D$150,"Content")=0,"",COUNTIFS(Sheet1!$A$2:$A$150,Sheet2!$B2,Sheet1!$C$2:$C$150,Sheet2!C$1,Sheet1!$D$2:$D$150,"Content"))

I am using the double countifs to not show 0's.

Here is what it looks like for Sheet1 Image

Here is how I would like Sheet 2 to look enter image description here

Any help or advise would be much appreciated.

2
excelcountuniqueformula

2 Answers

2
votes

The answer by Vijayakumar works. In case you need a simpler version (without the data model option, use this formula in column E dragged down

=SUMPRODUCT(--($B$1:B1=B2))>0
  1. Select all data
  2. Insert ->Pivot table
  3. In The Pivot table fields Move "SR" to Rows section "Stage" to column section "Prod" & "IsUnique" (Col E) to filter section and "QN" to Value section
  4. Now right click on some cell in Pivot which shows sum/count of QN Summarize values by -> Count
  5. In the filter section of pivot (first row on pivot table which states "Prod"), choose "Content"
  6. In the filter section of pivot (Second row on pivot table which states "IsUnique"), choose "TRUE"

enter image description here

When you have new data, you just need to Change Data Source from the Pivot Table tools->Analyze or Refresh. However you can record this whole process as macro to automate it.

1
votes

You can achieve this via Pivot table.

  1. Select all data
  2. Insert ->Pivot table
  3. In the pop up ensure you check the check box at the bottom with description "Add this data to data Model" and then click OK. This will create Sheet 2 for you.

  4. In The Pivot table fields Move "SR" to Rows section "Stage" to column section "Prod" to filter section "QN" to Value section

  5. Now right click on some cell in Pivot which shows sum/count of QN Summarize values by -> More optins

  6. In the pop up, scroll to bottom and click on "Distinct Count"
  7. In the filter section of pivot (first row on pivot table which states "Prod"), choose "Content"

Note: Distinct count won't appear in values section if step 3 is missed out. Hope this helps.