Average using results of Sumproduct

[norm01]

I have this Formula:
=SUMPRODUCT(--(OUTBOUND!$A$8:$A$99>=SUMMARY!$C$3),--(OUTBOUND!$A$8:$A$99<=SUMMARY!$D$3),--(OUTBOUND!$B$8:$B$99))
I need the result to be an average
Where would I be able to insert AVERAGE?

[NBVC]

Try:

Please Login or Register  to view this content.

[daddylonglegs]

You can use AVERAGE function like this

=AVERAGE(IF(OUTBOUND!$A$8:$A$99>=SUMMARY!$C$3,IF(OUTBOUND!$A$8:$A$99<=SUMMARY!$D$3,OUTBOUND!$B$8:$B$99)))

That's an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER

[norm01]

Thanks for the Formula but it returns a DIV/0!
Cheers!!!

[NBVC]

Did your original SUMPRODUCT return a value other than 0?

if it did, then the formula should not give you that error.

Also if you already have that result for your original, and assuming it is in, say X1, then you can shorten it with:

=X1/SUMPRODUCT(--(OUTBOUND!$A$8:$A$99>=SUMMARY!$C$3),--(OUTBOUND!$A$8:$A$99<=SUMMARY!$D$3))

[norm01]

Yes, it is pulling up data from OUTBOUND B8:B99 there are also some blanks

[NBVC]

So, you are saying you do get 0 with your original, because the all the values in column B that would match are blank?...

If you want 0 result... even if all values sum to 0, then...

Please Login or Register  to view this content.

If you still have issues, post the workbook if it is not confidential.

[norm01]

Here is the workbook

[NBVC]

See attached.

The formula I gave did work. It may have given wrong results, not errors, due to the fact that you have blanks in column B.. so I added an ISNUMBER() check.

The formula in C8 is:

Please Login or Register  to view this content.