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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies