# Sum value with same name

I have a Google Spreadsheet with a long list of names and numbers. Many of the names are duplicates but with different numbers in front of them. For example:

What will be the formula which will sum numbers of A, B, C, D and E? For example: the result of A will be 12. B will be 4 and so on.

You can do that by using the following formula:

``````=QUERY({B1:C5;D1:E5;F1:G5}, "SELECT Col1, SUM(Col2) GROUP BY Col1 LABEL SUM(Col2) ''")
``````

or

``````=QUERY({B:C;D:E;F:G}, "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''")
``````

See screenshot:

I've created an example file for you: sum value with same name

• this is complicated.i am unble to do it. can i send you file ? Nov 12, 2016 at 14:05
• @Saqib I've added an example file. You can make a copy for yourself and see how things work. Nov 12, 2016 at 15:13

If the first column you show is `A`, please try making a column of the alphabet (say starting in G18) and in H18:

``````=SUMIF(\$A\$12:\$E\$16,G18,\$B\$12:\$F\$16)
``````

copied down to suit.

SUMIF

# Formula

``````=ArrayFormula({
UNIQUE(FILTER({A:A;C:C;E:E},LEN({A:A;C:C;E:E}))),
mmult(
IF(
UNIQUE(FILTER({A:A;C:C;E:E},LEN({A:A;C:C;E:E})))
=
TRANSPOSE(FILTER({A:A;C:C;E:E},LEN({A:A;C:C;E:E}))),
TRANSPOSE(FILTER({B:B;D:D;F:F},LEN({A:A;C:C;E:E}))),
0
),
n(ISNUMBER(FILTER({B:B;D:D;F:F},LEN({A:A;C:C;E:E})))))
})
``````

# Explanation

"Just for fun", here is a third alternative.

On the answer by Jacob, QUERY is used. QUERY doesn't like columns where data type are mixed, so if you use text, dates and numbers as names, QUERY could not be the best option.

On the answer by pnuts, SUMIF is used. It requires to fill the row headers and do a fill down. This it's fine but could require more work when the source data be updated.