Add cells with other identical attributes

Solved/Closed
armatt - May 11, 2010 at 02:32 AM
 armatt - May 11, 2010 at 09:18 AM
I'm looking to create a formula which can be copied into other cells that will add numbers in one column, where their attributes in another column are the same, ie:

A B C
Andrew January 5
Andrew February 6
Barry January 12
Barry February 9
Barry March 1

What I mean is a formula that will identify rows with the same name in column A and add the corresponding values in column C - so a formula that would give 11 for Andrew, or 22 for Barry.

I have '=SUMIF(A$2:A$6,"Andrew",C$2:C$6)' - but i would have to change "Andrew" for every name, and that is unfeasible. I cannot change the layout of the spreadsheet.

Is there a way of getting excel to recognise identical names itself and then add the other corresponding columns?

1 reply

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
May 11, 2010 at 06:09 AM
insert a heading row.
data sample will be

name month amt
Andrew January 5
Andrew February 6
Barry January 12
Barry February 9
Barry March 1

by using advanced filter find out the UNIQUE names in column A
and park it some where below your data for example A9 down . it will be like this from A9 down

A9 name
A10 Andrew
A11 Barry

in B10(next to name andrew) copy paste this formula

=SUMPRODUCT(($A$2:$A$6=A10)*($C$2:$C$6))
copy B9 down as long as unique names are there.

you will get

name
Andrew 11
Barry 22
1
awesome, it worked perfectly. thank you!
0