condition decide the range and SUM of the multiple condition

i need a formula can decide a range according the between value and SUM of the frequency of the range.

sorry, so difficult to explain in English. please refer to the picture below.

thanks very much

Change B13 to 0, B14 to 41 and B15 to 81 and (assuming you want to look up average of the 5 columsn), then try In I3 :

=LOOKUP(LOOKUP(AVERAGE(D3:H3),$B$13:$C$15),{"A","B","C"},{1,2,3})

copied down

In M3, try:

=SUMPRODUCT(--($I$3:$I$11=M$2),--($A$3:$A$11=$L3))

copied down and across the table

i have trying use the formula on other table, the maximum value will decide the range, but it show me error.
=IF(B10=MANGO,LOOKUP(Q10,$V$3:$V$5,{"S","A","B"}),"")
refer to picture below.
Please help ......

See attached... Condition 2-1.xls

You need to include text string, Mango in double-quotes.

I also assumed you wanted to look for Kiwi...

so formula should be:

=IF(B10="MANGO",LOOKUP(Q10,$V$3:$V$5,$U$3:$U$5),IF(B10="Kiwi",LOOKUP(Q10,$V$16:$V$18,$U$16:$U$18),""))

However to make it cleaner and if you have multiple fruit tables, then you should re-arrange the table as per Condition 2-2.xls file and then name each table as per the fruits.. See Insert|Name|Define to see the 2 tables I created, then the formula in S10 would be simply:

=LOOKUP(Q10,INDIRECT(B10))

In both workbooks, the formula in E20 is:

=SUMPRODUCT(--($S$10:$S$16=E$18),--($R$10:$R$16=$B20)) copied down and across and in E24:

=SUMPRODUCT(--($S$10:$S$16=E$23),--($R$10:$R$16=$B24))

copied down and across.

When data E7 did not provide for =VLOOKUP(E17,$U$10:$V$21,2,FALSE) , it will become error on B21. it also will cause =SUMPRODUCT(--(S10:S17=E19),--(R10:R17=B21)) become error.
can i have a formula will not error occur when data haven't key in yet?

thanks.

Try, in R10

=IF(ISNUMBER(MATCH(E10,$U$10:$U$21,0)),VLOOKUP(E10,$U$10:$V$21,2,FALSE),"")

copied down