Averageifs - can't fix #div/0 error

[stacyslang]

I have a table with 3 worksheets, one of which has the summary data, one has validation data, and one has the individual records.


QA - Summary

QA - records

QA - Validation

As you can see - the formula on QA - summary tries to use data on QA - records to average the scores together. For some reason I keep getting #div/0. I tried breaking down the formula into a single AVERAGEIF and some of it works but some doesn't. I'd appreciate any help.

In case you can't read the formula:
=AVERAGEIFS(QA!E$2:E$59,QA!D$2:D$59,'QA - Summary'!A3, QA!B$2:B$47, "<>""", QA!A2:A59,">B1",QA!A2:A59,">='Call-Email-CSR-Date Validation'!E2",)

 

[wmc]

It looks like you're trying to average the CSR's score for phone calls if the date is on or after the start date and on or before the end date. This should work:

=AVERAGEIFS(QA!$E$2:$E$59,QA!$D$2:$D$59,'QA - Summary'!A3,QA!$B$2:$B$59,"<>0",QA!$A$2:$A$59,">="&'QA - Summary'!$B$1,QA!$A$2:$A$59,"<="&'Call-Email-CSR-Date Validation'!$E$2)

If you want it to display something other than #DIV/0! when someone has no qualifying scores, you can add an IFERROR statement.
=IFERROR(AVERAGEIFS(QA!$E$2:$E$59,QA!$D$2:$D$59,'QA - Summary'!A3,QA!$B$2:$B$59,"<>0",QA!$A$2:$A$59,">="&'QA - Summary'!$B$1,QA!$A$2:$A$59,"<="&'Call-Email-CSR-Date Validation'!$E$2),"no data")

 

[stacyslang]

Thanks very much for the help! Hadn't realized I needed the & as a connector!

Stacy