# Sum Range based on multiple criteria

#### jmabbott77

##### New Member
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?

#### Marcelo Branco

##### MrExcel MVP
Welcome to Mr Excel forum

Try

 A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ L​ M​ N​ O​ P​ Q​ 1​ Company​ Allocation​ 2013​ 2013​ 2013​ 2014​ 2014​ 2014​ 2015​ 2015​ 2015​ Company​ Allocation​ 2013​ 2014​ 2015​ 2​ PD​ Tech​ 2​ 2​ 2​ 2​ 2​ PD​ Tech​ 6​ 4​ 0​ 3​ PD​ Domain​ 3​ 3​ 3​ 3​ 3​ 3​ 3​ PD​ Domain​ 9​ 9​ 3​ 4​ PD​ GW​ 8​ 8​ 8​ 8​ PD​ GW​ 24​ 8​ 0​ 5​ Grav​ Tech​ 4​ 4​ 4​ 4​ 4​ 4​ 6​ Grav​ Domain​ 5​ 5​ 5​ 7​ Grav​ GW​ 9​ 9​ 9​ 9​ 8​ BSM​ Tech​ 6​ 6​ 6​ 6​ 9​ BSM​ Domain​ 7​ 7​ 7​ 10​ BSM​ GW​ 1​ 1​ 1​ 1​ 11​

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

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

#### Marcelo Branco

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### MrExcel MVP
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.

Last edited:

#### jmabbott77

##### New Member
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)

1,081,677
Messages
5,360,451
Members
400,586
Latest member
Minty

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...