SUMIFS with OR statement

[bluegold]

Hi guys, this one got me stumped as well

=SUMIFS(Football!AM:AM,Football!D:D,">="&$B$1,Football!D:D,"<="&$C$1,Football!A:A,">="&$F$1,Football!A:A,"<="&$G$1,Football!AO:AO,"<>nd")

How would I modify the above formula to include the following two statements?

Football!I:I,">1" OR Football!I:I,">0.05 AND < 1"

So I want all of the criteria from the original formula to stand but filter data from either of the additional 2 criteria

 

[NiMip]

I think I posted on a similar question you had the other day. If you're wanting to do any kind of OR with a SUMIFS, you need to use arrays and a SUM and an IF separately. The following should work, pressing CONTROL+SHIFT+ENTER instead of just enter:

=SUM(IF((Football!D:D>=$B$1)*(Football!D:D<=$C$1)*(Football!A:A>=$F$1)*(Football!A:A<=$G$1)*(Football!AO:AO<>"nd")*((Football!I:I>1)+(Football!I:I>0.05)*(Football!I:I<1)),Football!AM:AM))

Basically, a multiplication of arrays is an AND, and an addition of arrays is an OR. This formula does all the arithmetic with the arrays, and then everywhere there's a TRUE in the resulting array, the corresponding result in Football!AM:AM will stay as its value. Everywhere that's a FALSE, the corresponding result in Football!AM:AM will also be a FALSE. Summing the resulting Football!AM:AM array will then ignore all the FALSEs. Hope this makes sense.

 

[bluegold]

Thanks NiMip