0

hoping someone can help with this one:

The numbers on the left (phone numbers) will be either individual or repeated. Each row has a number in the second column that is associated with that phone number.

I need the sum of the values in the second column corresponding with each unique value in the first column. Currently, I select autosum, manually make my selection and obtain the summed value.

My list is usually 2500 rows so this can take some time. Is there any way to automate this?

  • Is it always sorted? – Scott Craner Jul 18 '16 at 23:20
  • Please update your question with the desired results for the example given. – techraf Jul 19 '16 at 0:42
  • Hi Scott & techraf, yes it is sorted. the desired results are in the third column, a sum of the values in second column corresponding with the numbers in the first column – stcg Jul 19 '16 at 1:14
  • Your example data are inconsistent. You show 725.75 for your second result, for 341423, but 500+50+100=650. You show 889.95 for your second result, for 687373, but 60+50+680+100=890. – Scott Jul 19 '16 at 1:40
  • Hmm.  You say that your data are sorted, but the sample data you show us are not. – Scott Jul 22 '16 at 20:17
1

Three solutions:

(I’m assuming that your data are in cells A1:B22.)

1. Conditional Formatting

  • Set C1 to =B1.
    (If your data don’t begin in column 1, and the row before the first data row doesn’t have numbers in it, you can use the next formula for the entire column.)
  • Set C2 to =IF(A1=A2, C1+B2, B2) and drag/fill down to C22.
    This will set column C to be a running total for the matching numbers;
    i.e., C1 = 50, C2 = 220, C3 = 320, C4 = 900, C5 = 500, etc.
  • Select the results (i.e., cells C1:C22) and do “Conditional Formatting” → “New Rule”.  Select “use a formula to determine which cells to format”, enter formula =A1=A2, and format the cell to be invisible.  (Common ways of doing this are to set the font color to white or to apply a custom number format of ;;;.)

In case the above isn’t clear: This puts a number in every cell in the range, but just hides the ones you don’t want.

2. Helper Column

  • Pick a column that’s out of the way; for example, column Z.  Define it the same as we defined column C, above.
  • Set C1 to =IF(A1=A2, "", Z1) and drag/fill down to C22.

3. All-in-One

  • Set C1 to =IF(A1=A2, "", SUMIF(A$1:A$22, A1, B$1:$B22)) and drag/fill down to C22.

Note that, if the phone numbers are not sorted properly, and are not divided into unique groups, these methods produce different results.  Labeling the methods as follows:

  1. Conditional Formatting
  2. Helper Column
  3. All-in-One

consider these data:

phone   value   method1   method2   method3
 ︙       ︙       ︙        ︙        ︙
 95      800     1500      1500      1500
 42        1                                ← First block of data for phone # 42
 42        2        3         3        99   ← Note that methods 1 and 2 yield 1 + 2 = 3
 17        4                                ↖ but method 3 yields 1 + 2 + 32 + 64 = 99
 17        8
 17       16       28        28        28
 42       32                                ← Second block of data for phone # 42
 42       64       96        96        99   ← Note that methods 1 and 2 yield 32 + 64 = 96
 83     1000                                ↖ but method 3 yields 1 + 2 + 32 + 64 = 99
 83     2000                                                                       (again)
 83     4000     7000      7000      7000
 ︙       ︙
  • 1
    exactly what i was looking for, thank you so much!!! the all in one did it :) – stcg Jul 19 '16 at 1:59
0

By using SUMIF, Here you go your example

Your Example

you can use this formula to an empty column corresponding to phone numbers column

=SUMIF($B$5:$B$14,E5,$C$5:$C$14)
0

The length shouldn't be a problem - this is a very common activity that I have had to do many times during my career. I use SUMIF function as follows:

  1. Grab the entire 1st column and put it in a new worksheet (col A) and sort it A-Z.
  2. Put in a cell B2 =IF(A1=A2,1,0) and autofill formulae down.
  3. Filter out all the '1' values and delete those rows - you are left with unique numbers (phone numbers)
  4. delete column B values and use SUMIF on each unique (phone) number. Range is fixed to 1st col, 1st sheet. Criteria is each number (phone number) from Col A. Sum_range is 2nd col, 1st sheet.

There may be a way of making this into a macro for yourself.

  • (1) Excel has more straightforward ways of removing duplicates; see this and this. (2) If I understand your answer correctly, you’re putting the sums on a different worksheet from the itemized data. This appears not to be what the OP wants. (3) Your answer would be more usable/useful if you actually showed what the SUMIF formula looks like, rather than just describing it. – Scott Jul 22 '16 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.