# Sumif, except if...

#### Alenz

##### New Member
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"),"")))

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

##### Well-known Member
Hi,
try this

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

#### 63falcondude

##### Well-known Member
This is untested. Try

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

#### Alenz

##### New Member
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".

##### Well-known Member
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

##### New Member
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.

##### Well-known Member
Do you care to send an example? I am sure that is a better option....

#### Alenz

##### New Member
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?

Replies
5
Views
108
Replies
12
Views
144
Replies
0
Views
281
Replies
1
Views
241
Replies
3
Views
662

1,191,719
Messages
5,988,290
Members
440,148
Latest member
sandy123

### 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.

### Which adblocker are you using?

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

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