Sumif, except if...

Alenz

New Member
Joined
Nov 20, 2016
Messages
10
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 :)
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi,
try this

=IF(A11<>"",SUMIFS(CW11:EA11,$CW$7:$EA$7,"Sunday",CW10:EA10,"<>"&"AL",CW10:EA10,"<>"&"SL",CW10:EA10,"<>"&"FRL"),"")
 
Upvote 0
This is untested. Try

=IF(A11<>"",SUMIFS(CW11:EA11,$CW$7:$EA$7,"Sunday",CW10:EA10,"<>AL",CW10:EA10,"<>SL",CW10:EA10,"<>FRL"),"")
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top