# Excel: sum values corresponding to multiple matching or individual numbers

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

## 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
︙       ︙
``````
• exactly what i was looking for, thank you so much!!! the all in one did it :) – stcg Jul 19 '16 at 1:59

By using SUMIF, Here you go 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)
``````

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