Forum Discussion

helenamh's avatar
helenamh
Copper Contributor
Jul 25, 2019

Help with MAXIFS with multiple criteria across multiple sheets

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!!

excel
Formulas and Functions

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 25, 2019

    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

    • helenamh's avatar
      helenamh
      Copper Contributor
      Jul 25, 2019

      SergeiBaklan 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? 

       

       

       

Resources