=sumproduct(averageifs(...

[HughJars100]

Hi,

I regularly use sumproduct(countifs( or sumproduct(sumifs where one of the criteria is a range and it works great.. However I am attempting to do this with averageifs and it doesnt work.. is there an alternative? or something I am missing something?

example of what im trying to make work...

=SUMPRODUCT(AVERAGEIFS(prob,estclose,">="&J$3,estclose,"<"&K$3,status,"open",owner,ISteam))

ISTeam is the range criteria

thanks

James

 

[sheetspread]

Can you divide the sum by the count?

E.g. : SUMPRODUCT(sumifs(prob,estclose,">="&J$3,estclose,"<"&K$3,status,"open",owner,ISteam))/SUMPRODUCT(countIFS(estclose,">="&J$3,estclose,"<"&K$3,status,"open",owner,ISteam))

?

Averageif(s) in an array will act the same as sumif except instead of zeroes you will get #div/0! errors

 

[HughJars100]

Hi Sheetspread,

I wasnt sure if I was doing something wrong.. and actually this is the solution I came up with too.. should have thought about it before I posted!

Thanks very much for replying!!