Using INDEX, MATCH, and SUMIF together

**Lea724** · 09-16-2011, 12:30 PM

Hi,

I'm attaching a spreadsheet with a simplified version of a problem I'm having. I would like to figure out how much principal is left on a building and how much interest is left on a building.

I figure it's going to be some combination of index, match, and sumif, but I'm having trouble putting them together so that they work in one formula.

A workaround (which I would prefer not to do if possible) is to do a sumif formula on Sheet2 to give me the totals that I need, then use index and match on Sheet1 to pull in those numbers.

Is there a way to combine the formulas so that I can have one formula on Sheet1 that does it all?

Thanks in advance,

Amanda

**ConneXionLost** · 09-16-2011, 12:56 PM

How about:

=SUMPRODUCT(--(Sheet2!$A$2:$L$2=B$2),((Sheet2!$A$3:$L$3=2011)+(Sheet2!$A$3:$L$3=2012)),INDEX(Sheet2!$A$1:$L$8,MATCH($A3,Sheet2!$A$1:$A$8,0),0))

autofill across & down.

Cheers,

**Lea724** · 09-19-2011, 11:45 AM

Thank you!

Copying and pasting that formula in definitely worked, though when I tried to manually type it out, I must've missed something because I wasn't getting the proper answer. Also, on the spreadsheet I actually need to use this on, the number of columns to be added together are more than just years 2011 and 2012 (it's more like 15 different columns). Is sumproduct still the best formula for summing many columns?

Thanks again for your help!

Amanda

**ConneXionLost** · 09-19-2011, 03:16 PM

It depends on the situation. If you're trying to use 15 different columns at once, then you might be better off with a different approach altogether.

Can you post another sample spreadsheet that better represents your circumstances?

Cheers,

**Lea724** · 09-19-2011, 03:26 PM

Hi,

I'm attaching another spreadsheet. I threw in years 2013-2024 to give a better representation for the scope of what I'm trying to do. The actual spreadsheet I want to apply this on goes from 2014 through 2037.

Thanks again for all of your help!

Amanda