Array formulas: nested ifs and same row calculation

Question

There are 2 inputs: A1 and B1.

In column D, there are many types of objects A. In column B, there are many types of objects B.

Here's what the formula is supposed to do:
If (D2 is 'A1' and G2 is 'B1') then, if (E2 is bigger than F2), subtract E2 and F2 (5 - 4, in this example), otherwise subtract F2 to E2 (like what happens in line 12).
If there is no match, don't do anything and just skip the row.

I would like to do this as an array formula (Ctrl+Shift+Enter), so it would sum everything in the end.

In this example, the output would be -1, because sum(and(5-4)(2-4)) .

So far, I have the following:

{=SUM(IF((D2:D12="A1")+(G2:G12="B1");E2:E12-F2:F12;0))} 

But it doesn't work properly as I'm not sure how Excel reads the subtraction part. I want to be able to subtract the values for the row where the combination was found.

Answer 1

If all you need is to have Column E subtracted by Column F for all matches then consider the following Array Formula:

=SUM((D2:D12=$B$2)*(G2:G12=$B$3)*(E2:E12-F2:F12))

(This can be updated with extra checks on what to subtract if needed)

This will SUM all of the subtractions (Column E) - (Column F) that contain a match to your inputs.

Here is the breakdown:

D2:D12=$B$2 and G2:G12=$B$3 will produce arrays containing 1's for a match and 0's for non-match:

{A1,A2,A3, -,A1, -, -,A4,A5,A1,A1}  {B1, -,B1, -,B4, -, -,B6,B5,B2,B1}
 V           V              V  V     V     V                       V
{1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1 }  {1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1 }

E2:E12-F2:F12 will result in a 3rd array consisting of the subtracted values:

 {5, 5, 3, 1, 3, 3, 7, 3, 9, 7, 4}
-{4, 3, 4, 5, 6, 5, 9, 6, 7, 8, 2}
={1, 2,-1,-4,-3,-2,-2,-3, 2,-1, 2}

Multiplying all of them will result like so:

 {1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1}
x{1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1}
x{1, 2,-1,-4,-3,-2,-2,-3, 2,-1, 2}
={1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2}

Then of course SUM will do it's job:

SUM({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2}) = 3