Sumproduct(Averageifs

[JamesBowdidge]

Hi,

I use a variation of this formula quite a lot.. essentially a sumifs (or countifs) surrounded by a sumproduct to use the range in one of the criteria instead of stacking several lookups together..

=SUMPRODUCT(SUMIFS(RAWDATA!$D:$D,RAWDATA!$A:$A,$A$40:$A$46,RAWDATA!$BR:$BR,B$4))

Where the bold range is a list of items to look up rather than a single item, comes in handy..

I want to do this with an averageifs, but it doesnt work... so it would look like this...

=SUMPRODUCT(averageifs(RAWDATA!$D:$D,RAWDATA!$A:$A,$A$40:$A$46,RAWDATA!$BR:$BR,B$4))

any ideas?

thanks

James

 

[Domenic]

The syntax is correct, so when you say that it doesn't work what exactly do you mean?

 

[JamesBowdidge]

Just returns a #div error...

 

[steve the fish]

It will mean at least one of your averageifs formulas is returning the error. You could add an iferror but then you need CTRL-SHIFT-ENTER too:

=SUMPRODUCT(IFERROR(AVERAGEIFS(RAWDATA!$D:$D,RAWDATA!$A:$A,$A$40:$A$46,RAWDATA!$BR:$BR,B$4),0))

 

[JamesBowdidge]

It will mean at least one of your averageifs formulas is returning the error. You could add an iferror but then you need CTRL-SHIFT-ENTER too:

=SUMPRODUCT(IFERROR(AVERAGEIFS(RAWDATA!$D:$D,RAWDATA!$A:$A,$A$40:$A$46,RAWDATA!$BR:$BR,B$4),0))

what a douchbag.. thanks steve, your absolutely right.. adding the iferror has fixed it.. much appreciated

thanks

J