Sum Excel Distinct Values

问题:

I know how to do this with MySQL, but am at a loss as to how (or even if possible) within MS Excel. I am running Excel version 14 on Windows (part of Office Professional 2010).

I have a table with two columns...for example one containing phone numbers and the other containing integers (length of phone calls). There are multiple calls from each phone number, so those numbers would likely appear multiple times. I am trying to SUM the total number of minutes per distinct phone number. How can I do this in Excel?

-------------------------
|  number  |  duration  |
-------------------------
| 1234567  |  8         |
| 2345671  |  5         |
| 1234567  |  12        |
| 3456712  |  5         |
| 3456712  |  47        |
| 1234567  |  15        |
| 1234567  |  4         |
-------------------------

So the end result would be something like:

-------------------------
|  number  |  duration  |
-------------------------
| 1234567  |  39        |
| 2345671  |  5         |
| 3456712  |  52        |
-------------------------

Thanks!

回答1:

Here is how to achieve this using pivot table.

I just created a 20 seconds screencast here.

回答2:

In column C use the SUMIF() Function like this:

=SUMIF(A:A,$A2,B:B)

This would give the total for every Phone#. I understand the benefits of the previous answers, but depending on the users needs this might be what is being sought.

回答3:

I'd go with a pivot table for this. Select your data (with headings) and click the Pivot Table button on the Insert ribbon. Use the "number" field for row labels box and "duration" for the values.

回答4:

I found something under the "Data" Tab in the "Outline" section called "Subtotal" which has done it. I just highlighted my two columns, and clicked "Subtotal" and it created group subtotals auto-magically.