SUMIFS with OR statement

[bluegold]

Hi guys this one is tricky, well for me at least!
I have the following SUMIFS statement that adds column AL when a bunch of conditions are met with my other columns. What I want to do is have an OR statement somehow nested so only one of the 2 highlighted red conditions need to be met. So all the conditions in black text have to be met but only either en!Q:Q,"=1" OR en!:P:P"=1" need to be met for my final condition.

=SUMIFS(en!AL:AL,en!D:D,">="&$B$1,en!D:D,"<="&$C$1,en!A:A,">="&$F$1,en!A:A,"<="&$G$1,en!P:P,"=D01",AO:AO,"<>nd",en!Q:Q,"=1" OR en!:P:P"=1")

Does this make sense?

 

[JackDanIce]

Do you mean:

=SUMIFS(en!AL:AL,en!D:D,">="&$B$1,en!D:D,"<="&$C$1,en!A:A,">="&$F$1,en!A:A,"<="&$G$1,en!P:P,"=D01",AO:AO,"<>nd")+sumif(en!Q:Q,1,en!AL:AL)+sumif(en!P:P,1,en!AL:AL)

 

[barry houdini]

You won’t be able to do that with a single SUMIFS

Either switch to SUMPRODUCT or use 3 SUMIFS - one with each of your OR conditions separately then subtract a SUMIFS with both conditions to avoid double counting - how many rows of data are there?

 

[bluegold]

There is no fixed number of rows its always increasing each day

 

[shift-del]

Bluegold,

I would suggest a helper column in sheet "en":

=OR(P2=1,Q2=1)

And use this helper column in your SUMIFS() with criteria TRUE.

 

[bluegold]

Ok thanks guys I now have 2 solutions, I will have a think about which one to use

 

[NiMip]

I would use the following

=SUM(IF((en!D:D>=$B$1)*(en!D:D<=$C$1)*(en!A:A>=$F$1)*(en!A:A<=$G$1)*(en!P:P="D01")*(AO:AO<>"nd")*((en!Q:Q=1)+(en!P:P=1)),en!AL:AL))

Pressing CONTROL+SHIFT+ENTER instead of just enter, as it's an array formula