# Calculate the common area between 2 products

#### Med4040

##### Board Regular

Hi guys,

I need to calculate the common area between 2 products

I have multiple of product that are made on series ofequipment. Each equipment has a surface area.

I need to calculate the shared area between 2 selected products. Some products may have more than more than one surface area (column12). So more specifically, the formula should calculate shared area between thelargest surface area of one product and largest surface area of another product
In this example, I am calculating the shared area between product A and D. the largest surface area of product A is 5 and the largest surface area of product D is 6. Two equipment (A and C) are common product A and D. Therefore, the common area between product A and product D is 2.

This is the formula I am using, but it the first product in the table
<style type="text/css">

table.tableizer-table{
font-size:12px;
border:1px solid #CCC ;
font-family:Arial, Helvetica, sans-serif;
}
.tableizer-tabletd {
margin:3px;
border:1px solid #CCC ;
}
.tableizer-tableth {
background-color:#104E8B;
color:#FFF;
font-weight:bold;
}
</style>

EquipmentName
Equipment Surface Area(m2)
Product A
Product A

Product B
Product C
Product C

Product D
Product D

Size
S
L
S&L
S
L
S
L
Equipment A

1
Y

Y

Y

Equipment B

1
Y
Equipment C

1
Y
Y

Y
Y
Y

Equipment D

2
Y
Y
Equipment E

2
Y
Y
Y
Equipment F

3
Y
Y
Equipment G

4
Y
Y
Product Surface Area (m2)

4
5
6
3
4
5
6

<tbody>
</tbody>

Last edited:

#### Med4040

##### Board Regular

Code:
``SUMPRODUCT((INDEX(Trains!C5:I12,,MATCH(E3,Trains!\$C\$1:\$I\$1,0))=INDEX(Trains!\$C\$5:\$I\$12,,MATCH(N3,Trains!\$C\$1:\$I\$1,0)))*(INDEX(Trains!\$C\$5:\$I\$12,,MATCH(N3,Trains!\$C\$1:\$I\$1,0))="Y")*Trains!\$B\$5:\$B\$12)``

#### Med4040

##### Board Regular
I hope someone helps with this.

Thank you

#### Eric W

##### MrExcel MVP
I found it easiest to create one formula to find the right column for each product (M4 and N4), then the formula to find the common area is straightforward:

ABCDEFGHIJKLMNO
1EquipmentNameEquipment Surface Area(m2)Product AProduct AProduct BProduct CProduct CProduct DProduct D
2SizeSLS&LSLSL
3Product AProduct D
4Equipment A1YYY27
5Equipment B1Y
6Equipment C1YYYYYCommon Area
7Equipment D2YY2
8Equipment E2YYY
9Equipment F3YY
10Equipment G4YY
11Product Surface Area (m2)4563456

</tbody>
Trains

Worksheet Formulas
CellFormula
M7=SUMPRODUCT(\$B\$4:\$B\$10,--(INDEX(\$C\$4:\$I\$10,0,M4)="Y"),--(INDEX(\$C\$4:\$I\$10,0,N4)="Y"))

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
M4{=MATCH(M3&"|"&MAX(IF(\$C\$1:\$I\$1=M3,\$C\$11:\$I\$11)),\$C\$1:\$I\$1&"|"&\$C\$11:\$I\$11,0)}
N4{=MATCH(N3&"|"&MAX(IF(\$C\$1:\$I\$1=N3,\$C\$11:\$I\$11)),\$C\$1:\$I\$1&"|"&\$C\$11:\$I\$11,0)}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

#### Med4040

##### Board Regular
The calculation is on another sheet called "Calculations". The data is on a sheet called "Trains"

On this sheet, I need to be able to select any 2 products.

This is my data table

Last edited:

#### Eric W

##### MrExcel MVP
Sorry, I thought you'd be able to change the ranges. Try these formulas on your Calculations sheet:

EFGHIJKLMN
3Product AProduct D
427
52

</tbody>
Calculations

Worksheet Formulas
CellFormula
E5=SUMPRODUCT(Trains!\$B\$5:\$B\$11,--(INDEX(Trains!\$C\$5:\$I\$11,0,E4)="Y"),--(INDEX(Trains!\$C\$5:\$I\$11,0,N4)="Y"))

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
N4{=MATCH(N3&"|"&MAX(IF(Trains!\$C\$1:\$I\$1=N3,Trains!\$C\$12:\$I\$12)),Trains!\$C\$1:\$I\$1&"|"&Trains!\$C\$12:\$I\$12,0)}
E4{=MATCH(E3&"|"&MAX(IF(Trains!\$C\$1:\$I\$1=E3,Trains!\$C\$12:\$I\$12)),Trains!\$C\$1:\$I\$1&"|"&Trains!\$C\$12:\$I\$12,0)}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The E4 and N4 formulas can be put anywhere. If you want to hide them, just do a custom format of ;;; and they will not show on the sheet. Then put in the E5 formula, again adjusting the ranges to your sheet.

#### Med4040

##### Board Regular
Thank you so much Eric. I really appreciate it. It is working great.

I am just trying to understand what do the formulas in E4 and N4 calculate? Also, should these cells be hidden? Because if I am going show these numbers, I need to explain what do they mean

Thanks again

#### Eric W

##### MrExcel MVP
The E4 formula figures out what column in your Trains table has the maximum area for "Product A" (or whatever is in E3). So in this example, it says column 2, which starting at column C, gives us column D. Same idea for N4. In theory, both of these formulas could be incorporated in the E5 formula, but it would be one long, awkward formula.

#### Med4040

##### Board Regular
Thank so again Eric for your help. I will just hide E4 and N4

Cheers

1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...