Home

Help with MAXIFS with multiple criteria across multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-773980%22%20slang%3D%22en-US%22%3EHelp%20with%20MAXIFS%20with%20multiple%20criteria%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773980%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20the%20highest%20value%20from%20one%20identical%20column%20across%20multiple%20sheets%2C%20based%20on%20multiple%20criteria%20(highest%20number%20of%20beneficiaries%20reached%20by%20any%20one%20activity%20within%20a%20specific%20location%20%5Bmust%20have%20the%20correct%20country%2C%20state%2C%20municipality%2C%20and%20community%5D)%2C%20but%20MAXIFS%20is%20returning%20%23VALUE.%20This%20is%20the%20formula%20I'm%20using%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMAXIFS(INDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!AK%3AAK%22)%2CINDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!A%3AA%22)%2C%24BE5%2CINDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!B%3AB%22)%2C%24BF5%2CINDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!C%3AC%22)%2C%24BG5%2CINDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!D%3AD%22)%2C%24BH5)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20an%20identical%20SUMPRODUCT(SUMIFS%20formula%20to%20find%20the%20total%20number%20of%20beneficiaries%20reached%20within%20each%20location%2C%20and%20the%20formula%20is%20working.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(SUMIFS(INDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!AK%3AAK%22)%2CINDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!A%3AA%22)%2C%24BE5%2C%20INDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!B%3AB%22)%2C%24BF5%2C%20INDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!C%3AC%22)%2C%24BG5%2C%20INDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!D%3AD%22)%2C%24BH5))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20on%20how%20to%20get%20MAXIFS%20to%20return%20the%20correct%20values%20is%20appreciated!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-773980%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774162%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20MAXIFS%20with%20multiple%20criteria%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774162%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382274%22%20target%3D%22_blank%22%3E%40helenamh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20formula%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(SUMIFS(INDIRECT(...%3C%2FPRE%3E%0A%3CP%3Eworks%2C%20copy%2Fpaste%20it%20as%20it%20is%2C%20and%20change%20on%3C%2FP%3E%0A%3CPRE%3E%3DMAX(MAXIFS(INDIRECT(...%3C%2FPRE%3E%0A%3CP%3EThus%20you'll%20be%20sure%20there%20are%20no%20other%20errors%20in%20the%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774237%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20MAXIFS%20with%20multiple%20criteria%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20the%20suggestion!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20reason%2C%20that%20approach%20works%20with%20other%20formulas%2C%20ex%3A%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%3DSUMPRODUCT(MAXIFS(INDIRECT%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3Ereturns%20an%20accurate%20value.%20However%2C%20when%20I%20use%20the%20same%20approach%20with%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%3DMAX(MAXIFS(%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3Eit%20consistently%20returns%20%23VALUE!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20other%20suggestions%20by%20any%20chance%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
helenamh
New Contributor

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 Replies

@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

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

 

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies