Sum Range based on multiple criteria

jmabbott77

New Member
Joined
Mar 15, 2018
Messages
6
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:

CompanyAllocation201320132013201420142014201520152015
PDTech22222
PDDomain3333333
PDGW8888
GravTech444444
GravDomain555
GravGW9999
BSMTech6666
BSMDomain777
BSMGW1111

<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:

CompanyAllocation201320142015
PDTech
PDDomain
PDGW
...

<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
Joined
Aug 23, 2010
Messages
16,317
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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
Joined
Mar 15, 2018
Messages
6
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
Joined
Aug 23, 2010
Messages
16,317
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
Joined
Mar 15, 2018
Messages
6
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
Joined
Aug 23, 2010
Messages
16,317
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
Joined
Aug 23, 2010
Messages
16,317
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
Joined
Mar 15, 2018
Messages
6
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)
 

Forum statistics

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

Some videos you may like

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...
Top