I really hope I can present my question in a way that makes sense, lol.
Working on a SUMIFS formula, and one of the conditions is dependent on a drop down the user has access to, at the top.
I want the user to have the ability to select "all" - and it sum everything. But I don't know how to insert that 'wildcard', if you will, into my formula.
I highlighted the part of the formula I'm having issues with. Assume the user selects "All" from D2 (dropdown). But "all" isn't a valid entry in Atlantic!H:H.... I'm wanting it to basically NOT filter, based on AtlanticH:H. Does that make sense?
=SUMIFS(Atlantic!C:C,Atlantic!G:G,"="&B3,Atlantic!M:M,IF(B2="All","<="&999,"<="&B2),Atlantic!H:H,IF(D2="All",Atlantic!H:H,"="&D2))
Hello, Try this;
=SUMPRODUCT(SUMIFS(Atlantic!C:C,Atlantic!G:G,"="&B3,Atlantic!M:M,IF(B2="All","<=999","<="&B2),Atlantic!H:H,IF(D2="All",{"*","<9E300"},"="&D2)))
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
One way would be to test D2 at the start of the formula and have 2 possible formulas based on that, e.g.
=IF(D2="All",SUMIFS(Atlantic!C:C,Atlantic!G:G,B3,Atlantic!M:M,"<="&IF(B2="All",999,B2)), SUMIFS(Atlantic!C:C,Atlantic!G:G,B3,Atlantic!M:M,"<="&IF(B2="All",999,B2),Atlantic!H:H,D2))
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks