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.

	FCST	Actual
	October	October
	2017	2017
Account 1
Account 2
Account 3
Account 4

<tbody>
</tbody>

The data is located in something such as the example below and includes all months for both FCST and Actual.

	FCST	FCST	Actual	Actual
	October	November	October	November
	2017	2017	2017	2017
Account 1	62	21	10	68
Account 1	35	32	62	65
Account 1	32	12	28	38
Account 2	94	24	63	54
Account 2	82	35	92	65
Account 2	82	95	45	57
Account 3
Account 3
Account 3
Account 4
Account 4
Account 4

<tbody>
</tbody>

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

	FCST	FCST	Actual	Actual
	October	November	October	November
	2017	2017	2017	2017
Account 1	62	21	10	68
Account 1	35	32	62	65
Account 1	32	12	28	38
Account 2	94	24	63	54
Account 2	82	35	92	65
Account 2	82	95	45	57
Account 3
Account 3
Account 3
Account 4
Account 4
Account 4

<tbody>
</tbody>

G1:I7

	FCST	Actual
	October	October
	2017	2017
Account 1	129	100
Account 2	258	200
Account 3	0	0
Account 4	0	0

<tbody>
</tbody>

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.