I’m getting an understandable #DIV/O! error message, with an AVERAGEIFS formula. I could easily solve the problem with an IFISERROR addition. The problem however is that I use the AVERAGEIFS formula in combination with IF AND. And everywhere I insert the IFISERROR addition, I get an formula error message.
The SUMPRODUCT works like a charm:
=IF(AND($B46="",$C46=""),0,
IF(AND($B46="",$C46<>0),"not applicable",
IF(AND($B46<>"",$C46<>""),SUMPRODUCT(($B$4:$B$43=$B46)*($C$4:$C$43=$C46)*G$4:G$43),
SUMIF($B$4:$B$43,$B46,G$4:G$43))))
The AVERAGEIFS however doesn’t work
=IF(AND($B56="",$C56=""),0,
IF(AND($B56="",$C56<>0),"not applicable",
IF(AND($B56<>"",$C56<>""),AVERAGEIFS(G$4:G$43,$B$4:$B$43,$B56,$C$4:$C$43,"="&$C56),
AVERAGEIF($B$4:$B$43,$B56,G$4:G$43))))
Can anyone help me and guide me where to insert the IFISERROR
Bookmarks