Sum based on multiple column headers and row

[jjramirez85]

Hi, I have a summary table similar to the one below. The worksheet has column headers with multiple criteria consolidating into the single row criteria.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]FCST[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]October[/TD]
[TD]October[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Account 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The data is located in something such as the example below and includes all months for both FCST and Actual.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]FCST[/TD]
[TD]FCST[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]October[/TD]
[TD]November[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Account 1[/TD]
[TD]62[/TD]
[TD]21[/TD]
[TD]10[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]Account 1[/TD]
[TD]35[/TD]
[TD]32[/TD]
[TD]62[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]Account 1[/TD]
[TD]32[/TD]
[TD]12[/TD]
[TD]28[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]Account 2[/TD]
[TD]94[/TD]
[TD]24[/TD]
[TD]63[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]Account 2[/TD]
[TD]82[/TD]
[TD]35[/TD]
[TD]92[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]Account 2[/TD]
[TD]82[/TD]
[TD]95[/TD]
[TD]45[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]Account 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What formula is best to be used in this situation? I have tried hlookup, concantenate, and sumifs. I cant seem to get it right. I had it solved in the past, but cant seem to remember.

In general, I am wanting to summarize the account data by consolidating the accounts based on the matching headers,

 

[Domenic]

Let's assume the following...

A1:E15

[TABLE="width: 500"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]FCST[/TD]
[TD="class: xl63, width: 64"]FCST[/TD]
[TD="class: xl63, width: 64"]Actual[/TD]
[TD="class: xl63, width: 64"]Actual[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]October[/TD]
[TD="class: xl63"]November[/TD]
[TD="class: xl63"]October[/TD]
[TD="class: xl63"]November[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]2017[/TD]
[TD="class: xl63, align: right"]2017[/TD]
[TD="class: xl63, align: right"]2017[/TD]
[TD="class: xl63, align: right"]2017[/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 1[/TD]
[TD="class: xl63, align: right"]62[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]68[/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 1[/TD]
[TD="class: xl63, align: right"]35[/TD]
[TD="class: xl63, align: right"]32[/TD]
[TD="class: xl63, align: right"]62[/TD]
[TD="class: xl63, align: right"]65[/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 1[/TD]
[TD="class: xl63, align: right"]32[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]28[/TD]
[TD="class: xl63, align: right"]38[/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 2[/TD]
[TD="class: xl63, align: right"]94[/TD]
[TD="class: xl63, align: right"]24[/TD]
[TD="class: xl63, align: right"]63[/TD]
[TD="class: xl63, align: right"]54[/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 2[/TD]
[TD="class: xl63, align: right"]82[/TD]
[TD="class: xl63, align: right"]35[/TD]
[TD="class: xl63, align: right"]92[/TD]
[TD="class: xl63, align: right"]65[/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 2[/TD]
[TD="class: xl63, align: right"]82[/TD]
[TD="class: xl63, align: right"]95[/TD]
[TD="class: xl63, align: right"]45[/TD]
[TD="class: xl63, align: right"]57[/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 4[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 4[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Account 4[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

G1:I7

[TABLE="width: 300"]
<tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]FCST[/TD]
[TD="class: xl65, width: 64"]Actual[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]October[/TD]
[TD="class: xl65"]October[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]2017[/TD]
[TD="class: xl65, align: right"]2017[/TD]
[/TR]
[TR]
[TD="class: xl65"]Account 1[/TD]
[TD="class: xl65, align: right"]129[/TD]
[TD="class: xl65, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl65"]Account 2[/TD]
[TD="class: xl65, align: right"]258[/TD]
[TD="class: xl65, align: right"]200[/TD]
[/TR]
[TR]
[TD="class: xl65"]Account 3[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Account 4[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Then try...

H4, confirmed with CONTROL+SHIFT+ENTER, copied down and across:

=SUMIF($A$4:$A$15,$G4,INDEX($B$4:$E$15,0,MATCH(1,IF($B$1:$E$1=H$1,IF($B$2:$E$2=H$2,IF($B$3:$E$3=H$3,1))),0)))

Hope this helps!

 

[jjramirez85]

I am receiving a value error.

 

[Domenic]

As I stated in my post, the formula must be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place braces {...} around the formula.