=NOT isn't working?!

Andy83UK

Board Regular
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?!

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try 'Sent Data Mar'!\$P:\$P,"<>OK" for the last criteria.

Looks like...

1.
Rich (BB code):
``````=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.
Rich (BB code):
``````=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")
``````

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

Thanks

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

Replies
2
Views
371
Replies
0
Views
425
Replies
1
Views
444
Replies
1
Views
179
Replies
11
Views
533

1,203,096
Messages
6,053,512
Members
444,669
Latest member
Renarian

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.

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