Index Match to Sum Multiple Columns

**phelbin** · 09-02-2015, 06:56 PM

I'm am trying to use Index Match to find the sum of certain columns based on the header (see attached example). I'm using this formula:

=SUM(INDEX(B4:E8,,MATCH("Forecast",B3:E3,0)))

My problem is that the equation sums only the first column that returns that specific header, and my lists will have multiple columns with the same titles of budget, forecast, etc. I'm wanting to add all of them together. I tried SUMIF, which added multiple columns but only the first row.

Any ideas?

Thanks!!

**XOR LX** · 09-02-2015, 06:59 PM

Hi.

=SUMPRODUCT((B3:E3="Forecast")*B4:E8)

Regards

**FDibbins** · 09-02-2015, 07:00 PM

Try this...
=SUMIF($B$3:$E$3,"forecast",B4:E4)

edit: XOR, you forgot to absolute the B3 range (in case they need to copy it down)

**AlKey** · 09-02-2015, 07:12 PM

Try these two

=SUMIF(B3:E3,"Forecast",B9:E9)

=SUM(INDEX((VLOOKUP("Total",A4:E9,{3,5},0)),0))

**phelbin** · 09-02-2015, 07:13 PM

FDibbins, I had tried that, but it only returns the top row.

XOR, that was exactly what I was looking for. I considered SUMPRODUCT but couldn't quite wrap my head around how to make it work here. It really is such a flexible function. Thanks so much!!

And...solved!