Excel, want to read a column and sum up the values that match in another column
I have the table below and i want it to read through columns A-B and then sum the values that match their names in columns C-D.
So that in a column elsewhere of a list of names i have the sum of their values from this table. For example Ed features in both columns with a value of 1 and 3, so would return a value of 4. Struggling to get this to work nicely. My actual table consists of 4 columns names and two sets of 4 columns with numbers.
A B C D
Steve Jeff 4 1
James Calum 1 1
Alex Andy 2 1
Peter Ed 0 3
Owen Harry 1 2
Jeff Steve 4 1
James Calum 1 1
Andy Alex 2 1
Peter Owen 0 3
Ed Harry 1 2
You can use SUMIF, e.g.
=SUMIF(A:B,"ed",C:D)
replace "ed" with a cell value which contains your name, e.g.
With names in F2 down use this in G2
=SUMIF(A:B,F2,C:D)
SUMIF works on a 1 to 1 basis so this only sums column C when column A matches and sums column D when B matches
You can also use where ranges are 4 columns wide
Your question says
My actual table consists of 4 columns names and two sets of 4 columns with numbers.
I understand that to mean that you have 12 columns, like this:
A B C D E F G H I J K L name name name name 0 0 0 0 0 0 0 0 ALFRED name name name 42 0 0 0 83 0 0 0 name name name name 0 0 0 0 0 0 0 0 name name name name 0 0 0 0 0 0 0 0 name name BLYTHE name 0 0 0 0 0 0 60 0 name name name name 0 0 0 0 0 0 0 0 name name name name 0 0 0 0 0 0 0 0 name name name BLYTHE 0 0 0 95 0 0 0 0 name BLYTHE name name 0 17 0 0 0 0 0 0 name name name name 0 0 0 0 0 0 0 0
and you want to get 125 (42+83) for ALFRED and 172 (17+95+60) for BLYTHE. In case it’s not obvious from barry houdini’s answer, you would do that with
=SUMIF(A:D, "ALFRED", E:H) + SUMIF(A:D, "ALFRED", I:L)
and
=SUMIF(A:D, "BLYTHE", E:H) + SUMIF(A:D, "BLYTHE", I:L)
etc.