3

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 :

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!

italy8868
  • 31
  • 1
  • 3
  • Not sure, he seems to be summing a separate column. If his numbers change from 1 to anything other than 1, that answer wouldn't apply. – wizlog Aug 03 '17 at 14:40
  • Paragraph form is a lousy way to present spreadsheet data.  Please [edit] your question to show data in tabular form.  Show a variety of cases, and explain the output you want for each. P.S. Is the number always 0 or 1?  If so, that seems like something that you should say.  If not, then give examples with other numbers and explain how you want them to be handled. – G-Man Says 'Reinstate Monica' Aug 05 '17 at 03:51

2 Answers2

1

Use SUMPRODUCT and COUNTIF:

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

enter image description here

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.

enter image description here

Scott Craner
  • 22,693
  • 3
  • 21
  • 25
  • 1
    could you also explain how that formula works? – wizlog Aug 03 '17 at 14:26
  • Thank you! This seems to work for now, but I have no idea how. It does solve my problem though, so thank you very much! – italy8868 Aug 04 '17 at 07:37
  • @italy8868 see edit. – Scott Craner Aug 04 '17 at 13:24
  • Here's how it works: COUNTIF(A1:A3,A1:A3) : Doing countif(range1, range2) is basically returning an array with how many occurrence each range1 element has on range2. i.e, let A1:A3 = [1, 2, 3, 2, 3, 2] then each element's occurrence would be: [1: 1, 2: 3, 3: 2, 2: 3, 3: 2, 2: 3] so the returned array is: [1, 3, 2, 3, 2, 3]. Notice that the order does not matter. 1/COUNTIF(A1:A3,A1:A3) : Is the inverse of these elements. i.e: [1/1, 1/3, 1/2, 1/3, 1/2, 1/3]. We are doing this for the next step, summing them all. – user8491363 Mar 07 '22 at 14:54
  • =SUMPRODUCT(B1:B3,1/COUNTIF(A1:A3,A1:A3)) : Let B1:B3 be [1, 1, 1]. It's sole purpose is to sum all the elements of 1/COUNTIF(A1:A3,A1:A3) by applying SUMPRODUCT with 1/COUNTIF(A1:A3,A1:A3). Now notice summing elements of 1/COUNTIF(A1:A3,A1:A3) will always result in count of UNIQUE values because You are doing 1/1 + (1/2 + 1/2) + (1/3 + 1/3 + 1/3) = 3. That's it. – user8491363 Mar 07 '22 at 14:57
  • I used more elements here : A1:A3 = [1, 2, 3, 2, 3, 2] to make my point clearer so it wouldn't actually be A1:A3. – user8491363 Mar 07 '22 at 14:58
1

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.

remove duplicates

Then, press OK on the next screen. press okay

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 & ""))

Community
  • 1
wizlog
  • 13,277
  • 24
  • 77
  • 116
  • Thank you, but I cannot delete the duplicates, because I have more data in the other columns, so I need the duplicates for some sums, and not for others, if that makes any sense... And I don't want to have to add new columns without the duplicates just for a sum, I already have tens of columns and hundreds of lines of data. – italy8868 Aug 04 '17 at 07:34
  • Why not just copy the data to a new sheet, then delete duplicates? Then you can remove the sheet. – wizlog Aug 04 '17 at 20:56