5

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:

sample data

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.

3 Answers 3

3

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: enter image description here

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

2
  • this is complicated.i am unble to do it. can i send you file ?
    – Saqib
    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.
    – Jacob Jan
    Nov 12, 2016 at 15:13
1

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.