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?

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][td="bgcolor: #DCE6F1"]

M​

[/td][td="bgcolor: #DCE6F1"]

N​

[/td][td="bgcolor: #DCE6F1"]

O​

[/td][td="bgcolor: #DCE6F1"]

P​

[/td][td="bgcolor: #DCE6F1"]

Q​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][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][td][/td][td]

Company​

[/td][td]

Allocation​

[/td][td]

2013​

[/td][td]

2014​

[/td][td]

2015​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][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][td][/td][td]

PD​

[/td][td]

Tech​

[/td][td]

6​

[/td][td]

4​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][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][td][/td][td]

PD​

[/td][td]

Domain​

[/td][td]

9​

[/td][td]

9​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][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][td][/td][td]

PD​

[/td][td]

GW​

[/td][td]

24​

[/td][td]

8​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in O2 copied across and down
=SUMPRODUCT(($A$2:$A$100=$M2)*($B$2:$B$100=$N2)*($C$1:$K$1=O$1)*$C$2:$K$100)

M.

 

[jmabbott77]

Wow! Amazingly quick response. Thank you!! However.... I'm getting the #N/A error

 

[Marcelo Branco]

The formula worked perfectly for me.

Questions about the data in C2:K10:
1. Are there formulas in the range C2:K10 that in some situations return ""?
2. Are there errors #N/A in the range C2:K10?

M.

 

[jmabbott77]

All the data in C2:K10 are numbers. There are no errors or "" results.
So the formula worked for me in the simplified model I posted, but it didn't work in the real model.
I tried parsing out the formula parts one at a time, and the results are not #N/A except when I add the very last bit: *$C$2:$K$100)

 

[Marcelo Branco]

All the data in C2:K10 are numbers. There are no errors or "" results.
So the formula worked for me in the simplified model I posted, but it didn't work in the real model.
I tried parsing out the formula parts one at a time, and the results are not #N/A except when I add the very last bit: *$C$2:$K$100)

Can you post the formula you are using?

M.

 

[jmabbott77]

This is my exact formula: =SUMPRODUCT(('Amort Schedule'!$A$3:$A$64='Intangibles Summary'!$B7)*('Amort Schedule'!$B$3:$B$64='Intangibles Summary'!$C7)*('Amort Schedule'!$BS$1:$FP$1='Intangibles Summary'!F$6)*'Amort Schedule'!$BS$3:$CD$61)

 

[Marcelo Branco]

This is my exact formula: =SUMPRODUCT(('Amort Schedule'!$A$3:$A$64='Intangibles Summary'!$B7)*('Amort Schedule'!$B$3:$B$64='Intangibles Summary'!$C7)*('Amort Schedule'!$BS$1:$FP$1='Intangibles Summary'!F$6)*'Amort Schedule'!$BS$3:$CD$61)

All the ranges must be same size - the last range (red) is not and should not include column B (Allocation)
Try change to
'Amort Schedule'!$CS$3:$CD$64)

M.

 

[Marcelo Branco]

I don't understand where are you data?
Why
Amort Schedule'!$BS$1:$FP$1='Intangibles Summary'!F$6)*'Amort Schedule'!$BS$3:$CD$61)

Tell us the exact location of your data

M.

 

[jmabbott77]

All the ranges must be same size

Ahhhhhh! This was key!! I didn't realize this. It works now!!
Brilliant. Many many thanks.

I aligned the last two ranges of the formula which did not match up before.
My final formula is: =SUMPRODUCT(('Amort Schedule'!$A$3:$A$64='Intangibles Summary'!$B7)*('Amort Schedule'!$B$3:$B$64='Intangibles Summary'!$C7)*('Amort Schedule'!$CE$1:$FP$1='Intangibles Summary'!G$6)*'Amort Schedule'!$CE$3:$FP$64)