OR function within the SUMIFS function
I would love to have OR function within the SUMIFS function so if it meet either of two (or more) criterias it will be true.
Tyrone Victor Glisson commented
SumProduct, although powerful, won't work the same as OR within SUMIFS.
Consider the following example: You'd like to add up weighting of bets on a Roulette table.
How many numbers are covered if you bet on Red and 1st Column? Some red numbers are also 1st column so you need to do it without overlap.
Sumproduct is good for if the criteria is in one column, but if you have dynamic criteria in multiple columns requiring an OR (e.g. bet could be a colour, number, column, dozen, even/odd etc.); then SUMPRODUCT isn't powerful enough to handle this efficiently without creating a massively complex formula.
if the OR function was allowed within a SUMIFS, this could allow you to specify OR criteria from multiple columns and easily add them together without overlapping, even if your condition's column differs.
John Jairo V commented
Just use COUNTIF(s)/SUMIF(s)/AVERAGEIF(s) with matrix constant:
Runólfur Birgir Leifsson commented
Yes but you have to meet all of the criterias (it is more like AND instead of OR). I have a case were I need the formula to meet a criteria that can be x, y or z but not a, b or c. And also I need a wildcard so it is difficult to use sumproduct.
Thomas K commented
Maybe i'm wrong, but don't sumifs actually sum if different criterias are met!
Use sumproduct(), it is much more flexible and will allow you do what you want