0

This is my data sample:

enter image description here

What I'm trying to do with a formula is to count the number of employees in column D of my table in relation with the data in my column B. If there are duplicates in B, the formula should count the different names in D as 1. In my data sample, I've highlighted in green the ones that should be counted to help with comprehension.

As you can see, the count ($G$4) should be 7. In this situation, the cell D10 is not counted as B9+D9 and B10+D10 are perfect duplicates. Therefore only counted once. And while B5 and B6 are duplicate case file number, D5 and D6 are different, and therefore it is counted twice.

I suppose this would require a variation of the COUNTIF formula, but for the life of me I can't seem to get it right.

English is my second language, but I've tried to be as clear as possible.

I work with Excel 2010.

Thank you!!

Improve this question
1
  • 1
    I count 7 your picture shows 7 but your words say 6, which is the correct answer? Commented May 27, 2020 at 14:50

1 Answer 1

Reset to default
1

Use:

=SUMPRODUCT(1/COUNTIFS(Table1[Case file],Table1[Case file],Table1[Employee],Table1[Employee]))

enter image description here

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

2 Comments

Thank you for your answer! Is there anyway to disregard the DIV/0 error when one of the cells is blank?
=SUMPRODUCT(1/COUNTIFS(Table1[Case file],IF(LEN(Table1[Case file])=0,"",Table1[Case file]),Table1[Employee],IF(LEN(Table1[Employee])=0,"",Table1[Employee]))) Depending on version you may need to array enter with Ctrl-Shift-Enter.

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.