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.
Aucun commentaire:
Enregistrer un commentaire