0

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.

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You can even create pivot for large set of rows and remove blanks from pivot table. This way, when data changes, you can simply update sheet and refresh pivot rather than change data source option
When I use the =SUMPRODUCT(--($B$1:B1=B2))>0 if the QN is unique ie doesn't match the number below it it returns a FALSE. If the number is not unique it returns a True. This is OK but does cause me to have to change the filter in line 6 to false instead of true. Did I miss a step or invert something that is causing this? - Shawn
I no longer have the excel file but i suggest to wrap the formula in a NOT() function if you want to invert the results.
1

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.

Improve this answer

3 Comments

Thank you for the response. I am hopeful that I can do this via Macro fully automated. Is there a way to use ta Pivot Table in a macro?
Yes, you can easily record a macro. In fact you can do this as one time work of pivot and create a simple macro to refresh pivot each time you open worksheet. Alternate is to record whole pivot table creation as a macro. both solutions work equally good.
here is how: pryor.com/blog/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.