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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Allocation[/TD]
[TD]2013[/TD]
[TD]2013[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2015[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]PD[/TD]
[TD]Tech[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PD[/TD]
[TD]Domain[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PD[/TD]
[TD]GW[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grav[/TD]
[TD]Tech[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Grav[/TD]
[TD]Domain[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grav[/TD]
[TD]GW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BSM[/TD]
[TD]Tech[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]BSM[/TD]
[TD]Domain[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BSM[/TD]
[TD]GW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Allocation[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]PD[/TD]
[TD]Tech[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PD[/TD]
[TD]Domain[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PD[/TD]
[TD]GW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.