Jul 25 2019 11:36 AM - edited Jul 25 2019 12:03 PM
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!!
Jul 25 2019 01:53 PM
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
Jul 25 2019 02:29 PM
@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?