• 505K Members
• 4,301 Online
• 602K Conversations

New Contributor

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

2 Replies

Re: Help with MAXIFS with multiple criteria across multiple sheets

`=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

Highlighted

Re: Help with MAXIFS with multiple criteria across multiple sheets

@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
50 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
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Discussion - Updating our interface with Fluent touches
Elliot Kirk in Discussions on
102 Replies