Previously posted but closed as duplicate with link to the question below

Original question: Sum values to the right of criteria in multicolumn range Proposed answer/duplicate: Sum values with same name

Original question I apologize in advance because I don't even know how to describe this best.

But, I have 3 3-column ranges in which the left columns are IDs, the middle columns are names, and the right column is quantity I'd like to sum up.

There is some overlap between ID's in left columns.

I created a column of sorted unique ID's but could not come up with formulas to populate the names and then sum the values.

Ideally, the formula would also work with "arrayformula" as the columns may get very lengthy.

Here's an example spreadsheet https://docs.google.com/spreadsheets/d/1z6qx4JGl-RGm3GExhEcxoTby7DTRSaRfLo-3VGwucvk/edit?usp=sharing


2 Answers 2


Use query() like this:

  { G1:I; J2:L; N2:P }, 
  "select Col1, Col2, sum(Col3) 
   where Col1 is not null 
   group by Col1, Col2", 

This is an array formula that creates the whole result table in one go. Place the formula in row 1 of a free column with another free column to the right.

  • Hi there, thank you. That's missing the name column. I updated my spreadsheet with an example
    – YBB
    Commented Jun 8, 2021 at 22:16
  • Edited the formula per your new requirements. Commented Jun 9, 2021 at 7:22
  • I appreciate it very much
    – YBB
    Commented Jun 15, 2021 at 2:26

Here is a modification to @doubleunary's formula above, accounting for your comment to him:

  { G1:I; J2:L; N2:P }, 
  "Select Col1, Col2, SUM(Col3) 
   WHERE Col1 Is Not Null 
   GROUP BY Col1, Col2 LABEL SUM(Col3) 'TOTAL QTY'", 

Make sure that you have three open columns and that you place the above formula in the top cell of the leftmost of those three columns.

If this works as expected, please credit doubleunary with "Best Answer."

  • thank you so much for the extra help and handing the prize to doublenary.
    – YBB
    Commented Jun 15, 2021 at 2:27
  • Glad it all worked out.
    – Erik Tyler
    Commented Jun 15, 2021 at 2:35

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.