This is going to be really ugly!

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,0)),$B3,Qualified),AVERAGE(IF(INDIRECT( VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,0))=$B3) *(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY ,3,0))<""),Qualified)),"-")--BiffMicrosoft Excel MVP"Brian" wrote in ... For right now I am going to stick with the nasty INDIRECTs until I can geta handle on this headache! I have tried several options with noluck...where exactly do you add the extra condition that it must be non-blank andnon-zero?=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,S tats_Type_ARRAY,3,FALSE)),$B3,Qualified),AVERAGE(I F(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY ,3,FALSE))=$B3,Qualified)),"-")