AVERAGEIFS with SUMPRODUCT

[figo12]

Hello people, I am trying to use AVERAGEIFS with multiple sheets, I am using these formula (taken from working SUMIFS formula):

=SUMPRODUCT(AVERAGEIFS(INDIRECT("'"&$A$16:$A$21&"'!E:E"),INDIRECT("'"&$A$16:$A$21&"'!C:C"),C3))

Where A16:A21 are the sheet references, E:E should be the column to average with D:D but not sure how to add D:D to the formula, C:C is the column with the criteria range and C3 is the criteria.

So far it gives me a Divide by 0 Error.

Thanks very much!

[amit.wilson]

Hi - can you upload a sample sheet to help explain the problem?

Cheers

[canapone]

Hi,

an attempt. Again a sample file would helpful

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&$A$16:$A$21&"'!E:E"),INDIRECT("'"&$A$16:$A$21&"'!C:C"),C3))/SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$16:$A$21&"'!C:C"),C3)),0)

Regards