1

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
Improve this question
1
  • 1
    to improve our answers, we right now need to know if there is a chance multiple lines hold the same # and "Partial Sold" and if yes, how to treat them :) Commented Dec 30, 2015 at 20:26

3 Answers 3

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

If OP does not have AGGREGATE function as stated in @Jeeped answer, will he have IFERROR()?
My version of the formula: =SUM(IFERROR((F66:F86="Closed")*(D66:D86/SUMIFS(D66:D86,F66:F86,"Partial Sold",C66:C86,C66:C86)),0))
Scott, I do have iferror function. Dirk,Brak brilliant solutions. Sometimes you need someone who think out of the vos. I was too submerged on theproblem to even think different. Thanks again!
it was made while i didn't know he may not have functions like this. Also I would not recommend this with bigger tables cus it may freeze excel for several seconds to minutes or even crash when done for whole columns. Better use a helper column to split everything up like jeeped mentioned!
Yep, it was my fault. Anyway, thanks for complete the question and the answer. Then in order to preclude crashing would be preferable to split the thing. By now I would use the matrix foprmula. But the point now is what is more efficient, Dirk or Brak solution? I know the result is the same, but does they even perform the same thing?
|
2

I too would recommend using a helper column. You don't need to use array formulas to get to the answer though. You can use the following in the next available column:

=IF(F66="Closed",IFERROR(D66/SUMIFS($D$66:$D$86,$F$66:$F$86,"Partial Sold",$C$66:$C$86,C66),0),0)

This will return values for everything that matches your criteria, and zeroes for everything else. Then you can just take the sum of this helper column as your final summation of rates.

Excel

If you really don't want to use a helper column, you can wrap the helper column formula in a SUM and swap out the individual cell references for arrays (i.e. swap F66 for $F$66:$F$86, and so on), then enter it as an array formula with Ctrl+Shift+Enter↵. The whole thing would look like this:

=SUM(IF($F$66:$F$86="Closed",IFERROR($D$66:$D$86/SUMIFS($D$66:$D$86,$F$66:$F$86,"Partial Sold",$C$66:$C$86,$C$66:$C$86),0),0))
Improve this answer

Comments

1

I do not see this being done without a helper column. In an unused column to the right of F66, put this array¹ formula.

=IF(AND(OR(C66=INDEX(C$66:C$86*(F$66:F$86="Partial Sold"), , )), F66="Closed"), D66/INDEX(D$66:D$86, AGGREGATE(15, 6, ROW($1:$21)/((C$66:C$86=C66)*(F$66:F$86="Partial Sold")), 1)), "")

Fill down as necessary. The result will be the sum of those 'helper' numbers.

        helper_sum_column

Even if this could be done in a single formula, the calculation overhead would likely be prohibitive. Splitting a portion of the array calculations off to a helper column that can directly reference the value in column C for another lookup reduces this significantly.

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

Improve this answer

2 Comments

As my Excel version is a little bit old I do not have the useful function AGGREGATE. Let me ask you, what is the difference if I use this formula instead of yours? =IF(AND(OR(C66=INDEX(C$66:C$86*(F$66:F$86="Partial Sold"),,)),F66="Closed"),D66/SUMIFS(D$66:D$86,C$66:C$86,C66,F$66:F$86,"Partial Sold"),"") I hope formatting will be ok now...
I do not see this being done without a helper column. -> challenge accomplished :P

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.