ekaplan323
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.
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.
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,DataT able[[Flig ht Code]:[Flight Code]],{"B","Y","E"},DataT able[[Uniq ue Code]:[Unique Code]],1,DataTable[[Passen ger 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.
=SUMIFS(DataTable[[Bus Leg Miles]:[Bus Leg Miles]],DataTable[[Serial]
Bus Leg Miles is the sumrange, the criteriaranges are the other items.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great work.
=SUMIFS(D5:D8,E5:E8,"y")+ SUMIFS(D5:D8,E5:E8,"b") + SUMIFS(D5:D8,E5:E8,"e")