how to select a cell if it has certain value in column next to it

0
votes

I have an excel workbook with 3 sheets:

Sheet 1 and Sheet 2

Columns A-G have text entered into them
Column H is a selection of either 1,2, 3 or H from a drop down list
Column I has numeric (currency) value entered into it (called year 1)
Column J has numeric (currency) value entered into it (called year 2)
Column K has numeric (currency) value entered into it (called year 3)
Column L has numeric (currency) value entered into it (called year 4)
Column M has numeric (currency) value entered into it (called year 5)
Column N has numeric (currency) value entered into it (called years 6-10)

Sheet 3 consists of a table.

I need a formula that will pick up the figures and transfer and add them into Sheet 3.

So I need to know the total of Year 1 (column I) if column H reads a number 1 to transfer into a cell on sheet 3, and then into a different cell the total of year 1 (column I) if column H reads a number 2 etc..

Any ideas?

1
excelexcel-formula

1 Answers

2
votes

This may suit:

=SUMIF(Sheet1!H:H,1,Sheet1!I:I)+SUMIF(Sheet2!H:H,1,Sheet2!I:I)

Edit - and for other cell (!):

=SUMIF(Sheet1!H:H,2,Sheet1!I:I)+SUMIF(Sheet2!H:H,2,Sheet2!I:I)

Adds totals from Sheet1 and Sheet2 of the sums of the row values in ColumnI where ColumnH contains the criterion (1 for one cell, 2 for the other) in the corresponding row.

=SUMIF