SUM all values in a column, where cells in a second column match the cells in a third column

Question

I am trying to use the SUMIF function to sum an amount where the codes in two other columns match eachother. However I am unsure how to build this formula and I haven't succeeded yet, I am getting confused that on sheet 1 there is one row and on sheet 2 multiple rows have to be checked.

Sheet 1:

Code         Amount
XXXXX-1481   8
XXXXX-1913   5
XXXXX-2013   8
XXXXX-8329   10

XXXXX-1481   11

XXXXX-1913   6

Sheet 2:

Code         Total amount
XXXXX-1481   
XXXXX-1913
XXXXX-2013
XXXXX-8329

I want to SUM all values in column 'Amount' on sheet 1 in column 'Total amount' on sheet 2, where the codes from sheet 1 and 2 match. The codes will occur multiple times in the table in sheet 1, but only once on sheet 2. The result should be a filled 'Total amount' column:

Code          Total amount
XXXXX-1481    19    
XXXXX-1913    11
XXXXX-2013    8
XXXXX-8329    10

I am curious how others would solve this. Thank you in advance,

Mark

David García Bodego David García Bodego · Accepted Answer · 2019-10-16T08:38:09

Try this one: Supposing (Sheet1 as first sheet, Sheet2 as second sheet and Code as Cell A1)

=SUMIF(Sheet1!$A$1:$A$9;"="&Sheet2!A1;Sheet1!$B$1:$B$9) on Cell B2 at Sheet 2

If you have more than 8 items, then make it bigger.

Hope it helps

SUM all values in a column, where cells in a second column match the cells in a third column

2 Answers