Sumif, except if...

[Alenz]

I have the following formula that works fine:
=IF(A11<>"",SUMIFS(CW11:EA11,$CW$7:$EA$7,"Sunday"),"")

However, I need to add that if CW10:EA10 contains the following "AL" or "SL" or FRL", that it does not add those cells indicating this in CW11:EA11 to the sum.

I tried this as a start but it only gives #value!:
=IF(CW10:EA10=AL,0,(IF(A11<>"",SUMIFS(CW11:E11,$CW$7:$EA$7,"Sunday"),"")))

Hope this makes sense? Please help? Just learning excel

 

[admiral100]

Hi,
try this

=IF(A11<>"",SUMIFS(CW11:EA11,$CW$7:$EA$7,"Sunday",CW10:EA10,"<>"&"AL",CW10:EA10,"<>"&"SL",CW10:EA10,"<>"&"FRL"),"")

 

[63falcondude]

This is untested. Try

=IF(A11<>"",SUMIFS(CW11:EA11,$CW$7:$EA$7,"Sunday",CW10:EA10,"<>AL",CW10:EA10,"<>SL",CW10:EA10,"<>FRL"),"")

 

[Alenz]

Hi,
try this

=IF(A11<>"",SUMIFS(CW11:EA11,$CW$7:$EA$7,"Sunday",CW10:EA10,"<>"&"AL",CW10:EA10,"<>"&"SL",CW10:EA10,"<>"&"FRL"),"")

This worked perfectly!Thank you!

Now I have the same problem but with an IF. I suppose I need to add an SUMIFFS?:

I have the following which works fine:
=(IF('Full time'!F11>'Full time'!$CM$1,('Full time'!F11-'Full time'!$CM$1),"0")*24)

But I need to add that if $CW$7:$EA$7,"Sunday" and/or F10="AL or "SL" or "FRL", then the answer must be "0".

Can someone assist please?

 

[admiral100]

Maybe this

=(IF(AND('Full time'!F11>'Full time'!$CM$1,$CW$7:$EA$7="Sunday",OR(F10="AL",F10="SL",F10="FRL")),('Full time'!F11-'Full time'!$CM$1),"0")*24)

 

[Alenz]

Maybe this

=(IF(AND('Full time'!F11>'Full time'!$CM$1,$CW$7:$EA$7="Sunday",OR(F10="AL",F10="SL",F10="FRL")),('Full time'!F11-'Full time'!$CM$1),"0")*24)

That's amazing,thank you! I just changed the $CW$7:$EA$7 part to $CW$7 as it only needed to look there. Gave #Value! with the full $CW$7:$EA$7. Therefore the correct way was:
=(IF(AND('Full time'!F11>'Full time'!$CM$1,$CW$7="Sunday",OR(F10="AL",F10="SL",F10="FRL")),('Full time'!F11-'Full time'!$CM$1),"0")*24)


Now for a level up I have this crazy long formula which you will see is the same as my last one that you added to, but in addition, for every other cell in the row to be added (F then H then J then L...):

=(IF('Full time'!F13>'Full time'!$CM$1,('Full time'!F13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!H13>'Full time'!$CM$1,('Full time'!H13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!J13>'Full time'!$CM$1,('Full time'!J13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!L13>'Full time'!$CM$1,('Full time'!L13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!N13>'Full time'!$CM$1,('Full time'!N13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!P13>'Full time'!$CM$1,('Full time'!P13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!R13>'Full time'!$CM$1,('Full time'!R13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!T13>'Full time'!$CM$1,('Full time'!T13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!V13>'Full time'!$CM$1,('Full time'!V13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!X13>'Full time'!$CM$1,('Full time'!X13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!Z13>'Full time'!$CM$1,('Full time'!Z13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AB13>'Full time'!$CM$1,('Full time'!AB13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AD13>'Full time'!$CM$1,('Full time'!AD13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AF13>'Full time'!$CM$1,('Full time'!AF13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AH13>'Full time'!$CM$1,('Full time'!AH13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AJ13>'Full time'!$CM$1,('Full time'!AJ13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AL13>'Full time'!$CM$1,('Full time'!AL13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AN13>'Full time'!$CM$1,('Full time'!AN13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AP13>'Full time'!$CM$1,('Full time'!AP13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AR13>'Full time'!$CM$1,('Full time'!AR13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AT13>'Full time'!$CM$1,('Full time'!AT13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AV13>'Full time'!$CM$1,('Full time'!AV13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AX13>'Full time'!$CM$1,('Full time'!AX13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!AZ13>'Full time'!$CM$1,('Full time'!AZ13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!BB13>'Full time'!$CM$1,('Full time'!BB13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!BD13>'Full time'!$CM$1,('Full time'!BD13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!BF13>'Full time'!$CM$1,('Full time'!BF13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!BH13>'Full time'!$CM$1,('Full time'!BH13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!BJ13>'Full time'!$CM$1,('Full time'!BJ13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!BL13>'Full time'!$CM$1,('Full time'!BL13-'Full time'!$CM$1),"0")*24)+(IF('Full time'!BN13>'Full time'!$CM$1,('Full time'!BN13-'Full time'!$CM$1),"0")*24)

Firstly, if there is a way to shorten this, that would help a lot!
Secondly, I also need to add the above exception when "Sunday", "AL", "SL", or "FRL" is present as you did with the previous, much shorter formula. The plan was to add your exception to each cell to be summed but this is such a headache.

Hope this makes sense? Haha.

 

[admiral100]

Do you care to send an example? I am sure that is a better option....

 

[Alenz]

Do you care to send an example? I am sure that is a better option....

I would love to share, it is getting too complex. Sadly I do not know how...Where can I attach or send?