HOW TO USE SUMPRODUCT, SUMIFS AND INDIRECT TOGETHER

[FATI]

I have 12 sheets that needs to be summarises based on months and withdrawals and item codes.
so i created a range name and named it Donors
I then proceeded to use this formulae. however, the answer ends up being #ref!
I8:I200 has the withdrawals, D8:D200 has the months, Table13[[#Headers],[Jan]] has the criteria-which is month,A8:A200 has the item code whiles Table4[@[Activity Code] has the criteria 2 which is the specific item code

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Donors&"'!$I$8:$I$200"), INDIRECT("'"&Donors&"'!$D$8:$D$200"),Table13[[#Headers],[Jan]],INDIRECT("'"&Donors&"'!$A$8:$A$200"),Table4[@[Activity Code]]))

Any help will be appreciated

 

[jasonb75]

The formula looks right, the only possible cause would be an incorrect sheet name in the Donors list, try checking the following.

All names listed in the Donors range must exist, you can't include names of sheets that you haven't created yet.

Check for typos in the names.

There can't be any blanks / empty cells in the Donors named range.

If you can't find the error, try using formula evaluation, after evaluating 3-4 steps, you should see the ranges change to either #VALUE! or #REF! in the first part of SUMIFS.
As an example, {#VALUE!;#VALUE!;#REF!;#VALUE!} would mean that the 1st, 2nd and 4th sheets are good, but the 3rd doesn't exist. This is the one that you would need to check for an error in the sheet name. This will be in the order of the names in the Donors list, not the order of the actual sheets if different.

 

[FATI]

It worked. Thank you so much