Help with MAXIFS with multiple criteria across multiple sheets

[helenamh]

Hello,

I am trying to get the highest value from one identical column across multiple sheets, based on multiple criteria (highest number of beneficiaries reached by any one activity within a specific location [must have the correct country, state, municipality, and community]), but MAXIFS is returning #VALUE. This is the formula I'm using:

=MAXIFS(INDIRECT("'"&Sheets&"'!AK:AK"),INDIRECT("'"&Sheets&"'!A:A"),$BE5,INDIRECT("'"&Sheets&"'!B:B"),$BF5,INDIRECT("'"&Sheets&"'!C:C"),$BG5,INDIRECT("'"&Sheets&"'!D:D"),$BH5)

I am using an identical SUMPRODUCT(SUMIFS formula to find the total number of beneficiaries reached within each location, and the formula is working. 

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!AK:AK"),INDIRECT("'"&Sheets&"'!A:A"),$BE5, INDIRECT("'"&Sheets&"'!B:B"),$BF5, INDIRECT("'"&Sheets&"'!C:C"),$BG5, INDIRECT("'"&Sheets&"'!D:D"),$BH5))

Any help on how to get MAXIFS to return the correct values is appreciated!!

[#2]

@helenamh 

If your formula

=SUMPRODUCT(SUMIFS(INDIRECT(...

works, copy/paste it as it is, and change on

=MAX(MAXIFS(INDIRECT(...

Thus you'll be sure there are no other errors in the formula

[#3]

@Sergei Baklan Thank you for the suggestion! 

For some reason, that approach works with other formulas, ex: 

=SUMPRODUCT(MAXIFS(INDIRECT

returns an accurate value. However, when I use the same approach with

=MAX(MAXIFS(   

it consistently returns #VALUE!

Do you have any other suggestions by any chance?