Sum Multiple Columns with Multiple Criteria

[invest4value]

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?

Product #		Region 1	Region 2	Region 3	Region 4	Region 5	Region 6	Region 7	Region 8	Region 9
1			0.75%
2						0.37%	0.13%
3				1.25%				0.72%
4						9.50%		0.62%
5			0.19%						0.25%	0.07%
6									0.44%
7				0.57%
8					0.33%				0.13%
9		0.25%
10				0.54%			6.50%
11			0.53%
12								0.51%
13			0.08%	0.43%
14								0.51%
15			0.23%	0.25%
16				0.48%
17				0.45%
18		0.41%							7.60%
19				0.41%
20				0.41%
21				0.39%
22									0.31%
23				0.38%

<tbody>
</tbody>

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.

 

[braindiesel]

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?

Product #		Region 1	Region 2	Region 3	Region 4	Region 5	Region 6	Region 7	Region 8	Region 9
1			0.75%
2						0.37%	0.13%
3				1.25%				0.72%
4						9.50%		0.62%
5			0.19%						0.25%	0.07%
6									0.44%
7				0.57%
8					0.33%				0.13%
9		0.25%
10				0.54%			6.50%
11			0.53%
12								0.51%
13			0.08%	0.43%
14								0.51%
15			0.23%	0.25%
16				0.48%
17				0.45%
18		0.41%							7.60%
19				0.41%
20				0.41%
21				0.39%
22									0.31%
23				0.38%

<tbody>
</tbody>

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.

Exactly which numbers are you adding together to make THIS true... For example, the sum of Region 1 & 2 is 0.51%.

 

[invest4value]

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%

 

[braindiesel]

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%

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)

 

[eke_rula]

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.

Your data is A1:J24, ex. M2="Region 2", N2="Region 3"
You try this array formula in M3:

=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!!!