Excel 2016 formula to include only unique values

Question

I am looking for a formula to help me add up numbers in one column, but only if the text in another column is a unique value.

Here is a simplified example :

I need to be able to count the cooks and the thieves every month, which is no problem with a sum.if function. But I also need the number of different people that work for me each month. It is easy to put the answers here, but too complicated for hundreds of people. And if I can do it, then Excel should be able to do it. I just can't find the logic for a formula.

So in January, the answer would be 3 (Marc, Laura and Peter) and in April 0. What formula could I use to count Marc as 1 in January for example. The numbers will always be 1 or 0 (As a person either is a cook or isn't). I did try to put numbers like 0,5 (Each Marc would count as half a person, but if I add a third category, he would be a third, etc.), but then the sums are off as well. Thanks for your help so far!

Answer 1

Use SUMPRODUCT and COUNTIF:

=SUMPRODUCT(B1:B3,1/COUNTIF(A1:A3,A1:A3))

---

To address your conditions use this array formula:

=SUM(IF(B2:B6=1,1/COUNTIFS($A$2:$A$6,$A$2:$A$6,B2:B6,1)))

being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Answer 2

Hello and welcome to superuser.

I think there is a simple answer to your question, and maybe a more complicated formula later on. Because you only have Ones beside your names, you can select the range (or the cells that have the names and counts), then go to the Data Tab, and select Remove Duplicates.

Then, press OK on the next screen.

Then you can either sum the rows, or simply select either of the columns and Excel will actually sum them for you. Just look on the bottom right of your screen.

Alternatively, you can use the following formula to count distinct values:

=SUMPRODUCT((A2:A5000 <> "")/COUNTIF(A2:A5000,A2:A5000 & ""))