counting formula

  • Thread starter Thread starter lukasrichardhermann
  • Start date Start date
L

lukasrichardhermann

Hi there,
can anybody tell me the fastest way as to how to calculate the following scenario:

I have 2 columns A + B and ca. 400 rows

In column A are names of people
In column B are numbers indicating how often the persons in column A have been named.

Eg:

A B
Mr Smith 594
Mr Jones 380
Jack 220
Mr Smith 250
Helen 77
Rebecca 580
Jack 500
..
..
..

So far so good.

But: Many names in column A appear more than once with different numbers inB. this is because the numbers were allocated over a longer period of time..

I want the numbers summed up for each person. In the example above the result should be only one row with Jack and the number 720. obviously this can be done with the SUM function but I dont want to go through the entire listand perform the same function every time again when a person appears more than once, especially since the list is ongoing.

Is there a way to automatically identify duplicates in column A and then sum up the corresponding numbers in column B?

Many thanks!
 
Hi,

Am Wed, 21 May 2014 10:11:35 -0700 (PDT) schrieb
(e-mail address removed):
A B
Mr Smith 594
Mr Jones 380
Jack 220
Mr Smith 250
Helen 77
Rebecca 580
Jack 500
Click to expand...

the easiest and fastest way is a Pivot-Table.
Insert Headers => Insert => Pivot-Table.
Drag Name to rows and numbers to values

Or with formula in C2:
=IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$400,A2,$B$2:$B$400),"")


Regards
Claus B.
 
Hi there,

can anybody tell me the fastest way as to how to calculate the following scenario:



I have 2 columns A + B and ca. 400 rows



In column A are names of people

In column B are numbers indicating how often the persons in column A havebeen named.



Eg:



A B

Mr Smith 594

Mr Jones 380

Jack 220

Mr Smith 250

Helen 77

Rebecca 580

Jack 500

.

.

.



So far so good.



But: Many names in column A appear more than once with different numbers in B. this is because the numbers were allocated over a longer period of time.



I want the numbers summed up for each person. In the example above the result should be only one row with Jack and the number 720. obviously this can be done with the SUM function but I dont want to go through the entire list and perform the same function every time again when a person appears more than once, especially since the list is ongoing.



Is there a way to automatically identify duplicates in column A and then sum up the corresponding numbers in column B?



Many thanks!
Click to expand...

As claus has perfectly described, use the pivot table.

step 1 : ensure that there is no hidden rows and columns between the date, nor there is blank columns in between.

step 2 : select the rows and column. ( ctrl + shift + right & ctrl + shift + down)

step 3 : u can see the "insert" in the ribbon, in insert select pivot table

step 4 : click on --"ok","ok","ok"

step 5 : drag names to rows, and the numbers to values.

step 6: right click and see the bottom most option, "value field setting" and change it to sum.

ur done.
 
Back
Top