Sum Range based on multiple criteria

[jmabbott77]

I cannot seem to figure this one out... tried sumifs, sumproduct, index/match. Here's what I'm trying to do:

Sum data in an array that matches 3 different criteria coming from both columns and rows. Maybe it's best to illustrate. Here's a simplified version:

Company	Allocation	2013	2013	2013	2014	2014	2014	2015	2015	2015
PD	Tech	2	2	2	2	2
PD	Domain	3	3	3	3	3	3	3
PD	GW	8	8	8	8
Grav	Tech				4	4	4	4	4	4
Grav	Domain				5	5	5
Grav	GW				9	9	9	9
BSM	Tech						6	6	6	6
BSM	Domain						7	7	7
BSM	GW						1	1	1	1

<tbody>
</tbody>

So I have the raw data which is Company, Allocation, and multiple columns of yearly data. I'd like this to summarize into Company, Allocation, and sum of the year. Note that the raw data may have row gaps or be mixed up and such so I can't just use a simple formula and fill down.
I'd like a formula that can lookup and match both company and allocation and year and then sum the year row that matches that company and allocation row. Like this:

Company	Allocation	2013	2014	2015
PD	Tech
PD	Domain
PD	GW
...

<tbody>
</tbody>

Column 2013 for PD Tech should end up with 6
Column 2014 for PD Tech should end up with 4
etc.

Does that make sense? Any idea on how to do this with a smart formula that can lookup, match, and sum?

 

[jmabbott77]

Darn it. Nevermind. The 2014 column, which I thought was working, only appears to be picking up one amount in the CE:FP range, rather than summing the whole applicable range. The 2015-2017 columns are not working at all.
Ugh. Time to throw in the towel.

 

[Marcelo Branco]

The formula should work. Check carefully all the ranges.

M.

 

[jtakw]

Hi,

The Year Columns in your Data Table and the Years in your Summary Table, exactly how are they entered?
As a Date?, or are they Just a Number?
Because 2017 (the number) Does Not equal to 2/3/2017 (a date, which is also a number, 42769) formatted to show year Only.