Link to home
Start Free TrialLog in
Avatar of ekaplan323
ekaplan323Flag for United States of America

asked on

Excel 2013 Structured Reference - SUMIFS Multiple Possible Criterion using OR?

I am pulling data using SumIFS and one of the criteria can be any one of 3 answers, "Y", "B", or "E".  Is this possible? Cant get the syntax to work.
Avatar of regmigrant
regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

how about:
=SUMIFS(D5:D8,E5:E8,"y")+ SUMIFS(D5:D8,E5:E8,"b") + SUMIFS(D5:D8,E5:E8,"e")
Avatar of ekaplan323

ASKER

I know I can do that, however, the formula has 3 or 4 other criteria and that makes for a long formula.  I am also using absolute values which adds to the length.
One way is this...

Assuming the criteria range is B2:B20 and range to be summed is A2:A20 then try this....
You can add more criteria inside the curly braces.

=SUMPRODUCT((ISNUMBER(SEARCH({"Y","B","E"},B2:B20)))*A2:A20)

Open in new window

SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here is the formula I put in the Letters after Flight Code not using all of the criteria just the first

=SUMIFS(DataTable[[Bus Leg Miles]:[Bus Leg Miles]],DataTable[[Serial]:[Serial]],'Paper Report Reconciliation'!$H15,DataTable[[Flight Code]:[Flight Code]],{"B","Y","E"},DataTable[[Unique Code]:[Unique Code]],1,DataTable[[Passenger Usage Code]:[Passenger Usage Code]],"Personal Entertainment",DataTable[[Both P&E Code]:[Both P&E Code]],"0")

Bus Leg Miles is the sumrange, the criteriaranges are the other items.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great work.