average with multiple criteria including sum instead of array

Question

I'm trying to bulid a formula that could do the following: calculate an average for a variable that meets couple conditions. The problem with this case is that I want the average to be calculated when a sum of investments in specified year, and types is within high (K4) and low (J4) values. In this example the expected result would be variable a = 15% - conditions would be year - 2015, type - b & c (from a range H8:H10, order of a,b,c may vary), sum of investments for previous criteria within 400 and 600. Here type c do not meet criteria becasue the sum of investments is 900, so out of range specified before. Type b has investments equal to 500 so the average is calculated. Any ideas how can I handle this? Thanks.

Answer 1

I have named the columns with ranges (type: data for column type D4:D21, invst: E4:E21, year:b4:b21, min:cell j4, max: cell k4, pcg:f4:f21) for ease of understanding. Paste this in h8 as array formula (for a with year filter) and name this cell "suma"

=SUMIFS(invst,type,H8,year,2015)  # for type a

paste this in h9 as array formula (for b) and name it sumb

=SUMIFS(invst,type,H9)

paste this in j8 (answer for type a) as array formula

=IFERROR(AVERAGE(IF(AND(suma>min,suma<max),IF(type=H8,pcg,""),"")),"")

paste this in j9 (answer for type b) as array formula

=IFERROR(AVERAGE(IF(AND(sumb>min,sumb<max),IF(type=H9,pcg,""),"")),"")

Answer 2

This is a first try at it, but the formula is still a huge monster. There might be a better, more elegant way. But, at least it works.

=(IF(AND(SUMIF($D$4:$D$21,$H$8,$E$4:$E$21)>=$J$4, SUMIF($D$4:$D$21,$H$8,$E$4:$E$21)<=$K$4), SUMIFS($F$4:$F$21,$B$4:$B$21,$H$4,$C$4:$C$21,$I$4,$D$4:$D$21,$H$8), 0) + IF(AND(SUMIF($D$4:$D$21,$H$9,$E$4:$E$21)>=$J$4, SUMIF($D$4:$D$21,$H$9,$E$4:$E$21)<=$K$4), SUMIFS($F$4:$F$21,$B$4:$B$21,$H$4,$C$4:$C$21,$I$4,$D$4:$D$21,$H$9), 0) + IF(AND(SUMIF($D$4:$D$21,$H$10,$E$4:$E$21)>=$J$4, SUMIF($D$4:$D$21,$H$10,$E$4:$E$21)<=$K$4), SUMIFS($F$4:$F$21,$B$4:$B$21,$H$4,$C$4:$C$21,$I$4,$D$4:$D$21,$H$10), 0)) / (COUNTIF($D$4:$D$21,$H$8)*--(IF(AND(SUMIF($D$4:$D$21,$H$8,$E$4:$E$21)>=$J$4, SUMIF($D$4:$D$21,$H$8,$E$4:$E$21)<=$K$4), SUMIFS($F$4:$F$21,$B$4:$B$21,$H$4,$C$4:$C$21,$I$4,$D$4:$D$21,$H$8), 0)<>0) + COUNTIF($D$4:$D$21,$H$9)*--(IF(AND(SUMIF($D$4:$D$21,$H$9,$E$4:$E$21)>=$J$4, SUMIF($D$4:$D$21,$H$9,$E$4:$E$21)<=$K$4), SUMIFS($F$4:$F$21,$B$4:$B$21,$H$4,$C$4:$C$21,$I$4,$D$4:$D$21,$H$9), 0)<>0) + COUNTIF($D$4:$D$21,$H$10)*--(IF(AND(SUMIF($D$4:$D$21,$H$10,$E$4:$E$21)>=$J$4, SUMIF($D$4:$D$21,$H$10,$E$4:$E$21)<=$K$4), SUMIFS($F$4:$F$21,$B$4:$B$21,$H$4,$C$4:$C$21,$I$4,$D$4:$D$21,$H$10), 0)<>0))

Here a screenshot: