How do I add cells from different sheet based on the value of one cell in a sheet?

[landog]

Hi all,

I've been looking but cannot find this in other posts. I am trying to add cells, across sheets, based on the value of other cells.

For example, in my workbook, there is a sheet "Actual Spend". I'd like to add a number from the column Operation (or Capital) to a cell in sheet "Budget MTD & YTD". That by itself is not the issue.

Where I get confused is how to find the code in "Actual Spend", column B, find the month in "Actual Spend", column D and then add the value found in "Actual Spend", column E (or F) to the corresponding column containing the month (e.g. column H) and code (e.g. row 9) in sheet "Budget MTD & YTD".

Does my explanation help? If not, please ask for clarification and I'll do my best to explain better.

Forum spreadsheet.xlsx

Thank you all for any assistance.

Regards,
George

[ChemistB]

Assuming there could be multiple matches, You might want SUMIFS

For H9 for example

=SUMIFS('Actual spend'!$B$2:$B$7, $B9, 'Actual Spend'!$D$2:$D$7, H$7, 'Actual Spend'!$F$2:$F$7)

However, your month abbreviations should be consistant (i.e. all 3 characters)

[JohnTopley]

In H9

=IFERROR(INDEX('Actual spend'!$E$2:$F$7,MATCH(1,($B9='Actual spend'!$B$2:$B$7)*('Actual spend'!$D$2:$D$7=H$8),0),1),"")

In h15

=IFERROR(INDEX('Actual spend'!$E$2:$F$7,MATCH(1,($B15='Actual spend'!$B$2:$B$7)*('Actual spend'!$D$2:$D$7=H$14),0),2),"")

Enter both formulae with Ctrl+Shift+Enter: Copy across and down

Make text months in both sheets consistent (Jan & Jan OR January and January)

[JohnTopley]

ChemistB is correct: use SUMIFS (as stupidly I only thought of one value per item!)

[landog]

Hi ChemistB & JohnTopley,

Thank you for the responses. I tried ChemistB's suggestion but am getting a #VALUE! error. I also made the changes you both suggested regarding consistency with the months, (thank you).

Forum spreadsheet.xlsx

Thank you again for the help and information.
George

[samba_ravi]

Try the below formula in H9 and copy across
=SUMIFS( 'Actual spend'!$F$2:$F$7,'Actual spend'!$B$2:$B$7,$B9, 'Actual spend'!$D$2:$D$7,H$8)

Please Login or Register  to view this content.

[landog]

nflsales,

Thank you! I had to tweak it a little, but that seems to be the solution.

I learn a lot form this forum with this being the first time I've asked for assistance. The response was terrific and I appreciate all the contributors. Someday, I hope to contribute as well!

Thank you again,
George

[ChemistB]

My mistake, I put the sum range last when it should have been first.