Search code examples
excelexcel-formulaarray-formulas

Nested array formulas

I want a summation of rates. Let me explain it: I would like to sum the numbers in column D that matches 2 conditions (green rows in excel). First one: column F equal to "closed". Second one: column C equal to those numbers which in turn matches the following condition: column F equal to "Partial Sold". At the same time, EACH number in the previous summation might be divided by the number of column D which matches "Partial Sold" in column F. There is the example with the table/figure I attached: (4510 / 9820) + (6500 / 9820) + (9100 / 15400) + (2388 / 2995) + (12400 / 9820) + (2904 / 5855). My cells would be: (D69 / D66) + (D70 / D66) + (D76 / D74) + (D82 / D78) + (D83 / D66) + (D84 / D72).

@Jeeped with your cells would be: (D6 / D3) + (D7 / D3) + (D13 / D11) + (D19 / D15) + (D20 / D3) + (D21 / D9) 

..     C      D        E           F

65     #     Total    Side    Condition
66     1     9820     Buy     Partial Sold
67     2     3850     Buy     Closed
68     3     7151     Buy     Partial Sold
69     1     4510     Sell    Closed
70     1     6500     Sell    Closed
71     4     8180     Buy     Open
72     5     5855     Buy     Partial Sold
73     6     2553     Buy     Open
74     7     15400    Buy     Partial Sold
75     2     4600     Sell    Closed
76     7     9100     Sell    Closed
77     8     7531     Buy     Open
78     9     2995     Buy     Partial Sold
79     3     3000     Sell    Closed
80     10    8691     Buy     Open
81     3     2500     Sell    Closed
82     9     2388     Sell    Closed
83     1     12400    Sell    Closed
84     5     2904     Sell    Closed
85     11    3848     Buy     Open
86     12    7745     Buy     Open
Solution

  • To do it in one step with an array formula you could use:

    =SUM(IFERROR((D66:D86*(F66:F86="Closed"))/((C66:C86=TRANSPOSE(C66:C86))*TRANSPOSE(D66:D86*(F66:F86="Partial Sold"))),0))

    This is an array formula and must be confirmed with Ctrl+Shift+Enter↵.

    it will generate a 2D-array holding the original values for closed as rows and divides this 1D array by this:

    • buils up 2D-array by col C = transposed col C
    • multiply each row by col D
    • set all items in each row to 0 if not "Partial Sold"

    for each div by 0 the IFERROR will set it to 0
    and this all in the SUM will give you your output

    enter image description here