SUMIFS with mutiple worksheets

Hello guys, I have been trying to use sumifs function on multiple worksheets without success, I have researching and trying to apply this formula, it is not working as well and also need to use more criteria (sumifs instead):

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$16:$A$21&"'!D:D"),A3,INDIRECT("'"&$A$16:$A$21&"'!D:D")))

where A16:A21 is the sheetlist range, D:D the relevant sum range in every sheet (A16:A21), and A3 (Paul actual cell value) is criteria Value #1, but it is located on column C:C on each sheet list. And not sure how to add it to formula, I mean I need it to convert it to a SUMIFS formula.

Thanks very much!

Originally Posted by daddylonglegs

Apologies, I messed up the syntax, should be

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$16:$A$21&"'!D:D"),INDIRECT("'"&$A$16:$A$21&"'!C:C"),A3))

That should be matching A3 against column C in each sheet and summing col D values when that matches

Thanks it works great!! Thanks again for everything and I will mark thread as solved!