have four columns:
| Name A1 | Count B1 | Name C1 | Count D1 |
|---|---|---|---|
| Audi | 4 | MB | 10 |
| Bmw | 7 | Toyota | 15 |
| MB | 15 | Audi | 12 |
| Toyota | 12 | vW | 3 |
| VW | 10 | BMW | 8 |
What formula can I use to subtract the number of D1 from the number of column B1 according to the name of C1?
example
| Name A1 | Count B1 | Name C1 | Count D1 | Difference |
|---|---|---|---|---|
| Audi | 4 | MB | 10 | 10 - 15 = -5 |
| Bmw | 7 | Toyota | 15 | 15 - 12 = 3 |
| MB | 15 | Audi | 12 | 12 - 4 = 8 |
| Toyota | 12 | vW | 3 | 3 - 10 = -7 |
| VW | 10 | BMW | 8 | 8 - 7 = 1 |
CodePudding user response：
You can use this formula:
=D2-INDEX($B$2:$B$6,MATCH(C2,$A$2:$A$6,0))
CodePudding user response：
Using SUMPRODUCT()
• Formula used in cell E2
=D2-SUMPRODUCT((C2=$A$2:$A$6)*($B$2:$B$6))