Sum Multiple Columns with Multiple Criteria

I

invest4value

New Member
Joined
Mar 8, 2017
Messages
2
Say I have the following chart (See below) and I'm trying to sum the regions that have product overlap. What type of SumIf or SumProduct formula could I use to accomplish this task?

[TABLE="width: 704"]
<tbody>[TR]
[TD]Product #[/TD]
[TD][/TD]
[TD]Region 1[/TD]
[TD]Region 2[/TD]
[TD]Region 3[/TD]
[TD]Region 4[/TD]
[TD] Region 5[/TD]
[TD]Region 6[/TD]
[TD]Region 7[/TD]
[TD]Region 8[/TD]
[TD]Region 9[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.75%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.37%[/TD]
[TD]0.13%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.72%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9.50%[/TD]
[TD][/TD]
[TD]0.62%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.19%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.25%[/TD]
[TD]0.07%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.44%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.57%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.33%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.13%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]0.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.54%[/TD]
[TD][/TD]
[TD][/TD]
[TD]6.50%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.53%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.51%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.08%[/TD]
[TD]0.43%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.51%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.23%[/TD]
[TD]0.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.48%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.45%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]0.41%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7.60%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.41%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.41%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.39%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.31%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.38%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want to find the overlap between Region 1 & 2, Region 1 & 3, Region 1 & 4, etc.

For example, the sum of Region 1 & 2 is 0.51%. What would be the Excel formula to help me calculate that amount?

I'm looking to find the sum between two regions?

Thanks for your help.
 
Sort by date Sort by votes
invest4value said:
Say I have the following chart (See below) and I'm trying to sum the regions that have product overlap. What type of SumIf or SumProduct formula could I use to accomplish this task?

[TABLE="width: 704"]
<tbody>[TR]
[TD]Product #[/TD]
[TD][/TD]
[TD]Region 1[/TD]
[TD]Region 2[/TD]
[TD]Region 3[/TD]
[TD]Region 4[/TD]
[TD] Region 5[/TD]
[TD]Region 6[/TD]
[TD]Region 7[/TD]
[TD]Region 8[/TD]
[TD]Region 9[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.75%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.37%[/TD]
[TD]0.13%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.72%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9.50%[/TD]
[TD][/TD]
[TD]0.62%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.19%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.25%[/TD]
[TD]0.07%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.44%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.57%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.33%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.13%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]0.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.54%[/TD]
[TD][/TD]
[TD][/TD]
[TD]6.50%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.53%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.51%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.08%[/TD]
[TD]0.43%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.51%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.23%[/TD]
[TD]0.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.48%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.45%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]0.41%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7.60%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.41%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.41%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.39%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.31%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.38%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want to find the overlap between Region 1 & 2, Region 1 & 3, Region 1 & 4, etc.

For example, the sum of Region 1 & 2 is 0.51%. What would be the Excel formula to help me calculate that amount?

I'm looking to find the sum between two regions?

Thanks for your help.
Click to expand...

Exactly which numbers are you adding together to make THIS true... For example, the sum of Region 1 & 2 is 0.51%.
 
Upvote 0
Sorry, the sum is between Region 2 & 3. 0.08% + 0.43% = 0.51%

Another example would be the sum between Region 5 & 7. 9.50% + 0.62% = 10.12%
 
Upvote 0
invest4value said:
Sorry, the sum is between Region 2 & 3. 0.08% + 0.43% = 0.51%

Another example would be the sum between Region 5 & 7. 9.50% + 0.62% = 10.12%
Click to expand...
OK so how what are you trying to do beyond a SUM for each row?
Are you looking for a quick solution to add every possible combination of 9 numbers paired?

I am confused because Product 5 has 3 percents... do you need to total the three individual pairings or add up all 3 (a SUM formula)
 
Last edited:
Upvote 0
invest4value said:
Say I have the following chart (See below) and I'm trying to sum the regions that have product overlap. What type of SumIf or SumProduct formula could I use to accomplish this task?


I want to find the overlap between Region 1 & 2, Region 1 & 3, Region 1 & 4, etc.

For example, the sum of Region 1 & 2 is 0.51%. What would be the Excel formula to help me calculate that amount?

I'm looking to find the sum between two regions?

Thanks for your help.
Click to expand...
Your data is A1:J24, ex. M2="Region 2", N2="Region 3"
You try this array formula in M3:
PHP: 
=SUM((A2:A24=MATCH(TRUE,MMULT(--(COUNTIF(M2:N2,B1:J1)*B2:J24>0),TRANSPOSE(COLUMN(B1:J1))^0)=2,))*COUNTIF(M2:N2,B1:J1)*B2:J24)
Entered with Ctrl+Shift+Enter!!!
Hope to help you!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Ranking with NET and with in the group
Replies
7
Views
160
sksanjeev786
S
JenniferMurphy
  • Solved
Help with CountIfs statement regarding a table
Replies
4
Views
270
JenniferMurphy
JenniferMurphy
M
  • Solved
Sumifs Formula not working
2
Replies
15
Views
1K
Peter_SSs
Peter_SSs
A
  • Solved
VBA - filter based on specific value then copy relevant values in other columns
2 3
Replies
24
Views
2K
Ananthak275
A
1
  • Question Question
Calculate on every third Friday of the month and show results between positive & negative weeks
2
Replies
14
Views
1K
15minoffame
1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top