=NOT isn't working?!

[Andy83UK]

Hi,

I've been working on a series of cells with similar formulas in to compare large amount of data quickly however I can't make the =NOT part work.

The base formula I use which is working is

=IF(NOT('Sent data Mar'!$L:$L="Minor"),COUNTIFS('Sent data Mar'!$D:$D,"Operations",'Sent data Mar'!$B:$B,"Merseyside",'Sent data Mar'!$M:$M,"ASSET ACTIVITY & REINSTATEMENT IF REQD",'Sent data Mar'!$K:$K,"Works Stop",'Sent data Mar'!$U:$U,"N",'Sent data Mar'!$P:$P,"OK"))

However when I add in a NOT component

=IF(NOT('Sent data Mar'!$L:$L="Minor"),COUNTIFS('Sent data Mar'!$D:$D,"Operations",'Sent data Mar'!$B:$B,"Merseyside",'Sent data Mar'!$M:$M,"ASSET ACTIVITY & REINSTATEMENT IF REQD",'Sent data Mar'!$K:$K,"Works Stop",'Sent data Mar'!$U:$U,"N",'Sent data Mar'!$P:$P,NOT('Sent data Mar'!$P:$P="OK")))

this doesn't work, I dont know what I'm doing wrong, is it because the formula is not able to work within a "countifs" formula?!

 

[Norie]

Try 'Sent Data Mar'!$P:$P,"<>OK" for the last criteria.

 

[Aladin Akyurek]

Looks like...

1.

=COUNTIFS(
  'Sent data Mar'!$L:$L, "<>Minor",
  'Sent data Mar'!$D:$D,"Operations",
  'Sent data Mar'!$B:$B,"Merseyside",
  'Sent data Mar'!$M:$M,"ASSET ACTIVITY & REINSTATEMENT IF REQD",
  'Sent data Mar'!$K:$K,"Works Stop",
  'Sent data Mar'!$U:$U,"N",
  'Sent data Mar'!$P:$P,"OK")

2.

=COUNTIFS(
  'Sent data Mar'!$L:$L, "<>Minor",
  'Sent data Mar'!$D:$D,"Operations",
  'Sent data Mar'!$B:$B,"Merseyside",
  'Sent data Mar'!$M:$M,"ASSET ACTIVITY & REINSTATEMENT IF REQD",
  'Sent data Mar'!$K:$K,"Works Stop",
  'Sent data Mar'!$U:$U,"N",
  'Sent data Mar'!$P:$P,"<>OK")

 

[Andy83UK]

Thank you thats it fixed! never even occurred to me to use <>

Thanks

 

[Aladin Akyurek]

Thank you thats it fixed! never even occurred to me to use <>

Thanks

Don't forget to move the conditional with the <> Minor test into CountIfs...