Search code examples
excelsumexcel-2013subtotal

Excel - Transferring Subtotals According to Criteria

I want to automate cells in another workbook using values that come from subtotals. 

Cities  Value1      Value2      Value3      Value4
Paris   15          2.5         65          0.285087719
Paris   16          3           66          0.347368421
London  18          4           68          0.477192982
London  19          4.5         69          0.544736842
Austin  22          6           72          0.757894737
Austin  23          6.5         73          0.83245614
Orlando 28          9           78          1.231578947
Orlando 29          9.5         79          1.316666667

                                570         5.792982456

I spaced out for readability, but Cities is A1, Value1 is B1, Value2 is C1, etc. Value1 and Value3 are just manual inputs. Value2 is =(Value1 - 10)/2, and Value4 is (Value2*Value3) / D11. The 570 (D11) is =Subtotal(9, D2:D9), and 5.79... is =Subtotal(9, E2:E9).

My problem is that I want the subtotal of Value4 for each city in Book2 (all data is from Book1). The issue with it is that the values in Value4 change off of D11, which changes when filtering for each city. This makes it so that I can't just use SUMIF on Value4 to divide each city in Book2 using criteria.

I then tried to break it up by using a SUMPRODUCT on Value2 and Value3 with an Index/Match for filtering, and then dividing by D11, as this would theoretically be the same as corresponding Value4 cells. 

=SUMPRODUCT(INDEX('[Book1]Sheet1'!$C$2:$C$9, MATCH("Paris", '[Book1]Sheet1'! 
$A$2:$A$9, 0)), INDEX('[Book1]Sheet1'!$D$2:$D$9, MATCH("Paris", '[Book1]
Sheet1'!$A$2:$A$9, 0))) 
/ IF(INDEX('[Book1]Sheet1'!$D$2:$D$9, MATCH("Paris", '[Book1]Sheet1'!
$A$2:$A$741, 0)), '[Book1]Sheet1'!$D$11, 0)

However, this also falls prey to changing based off of the filtering in Book1. I've also tried a myriad of combos with INDEX/MATCH and SUMIFS, but nothing seems to work and I'm at a complete loss. I haven't had any luck with pivots or pasting special either. Any help would be appreciated. Please let me know if additional clarification is necessary.

Solution

  • Try:

    =SUMPRODUCT(($A$2:$A$9=L20) * $C$2:$C$9 * $D$2:$D$9/SUMIF($A$2:$A$9,L20,$D$2:$D$9))

    where L20:L23 = the city names

    I'm not sure if this is what you want, but the above formula will give the results that would be obtained by filtering the table in Book 1 for each city individually.

    You'll need to add proper book and sheet references to the formula.

    Based on your posted data, here are the results:

    enter image description here