Thanks:  0
Likes:  0

# Thread: Countifs where a column doe not equal x

1. ## Countifs where a column doe not equal x

Hi All I have read and read and tried and tried but cant get formula to work.

Basically the below is the written formula

=COUNTIFS(Data!\$H:\$H,'All Dates'!\$B3,Data!\$I:\$I,'All Dates'!\$C\$2,Data!\$F:\$F,'Month select 1'!C16,Data!J:J,"<>Voided")

It all works but I need to exclude from the count Anywhere in column DATA!J:J where the entry is Voided or Possible Void

The formula when you remove that last criteria works and returns the correct number in the count but when I add the last criteria it returns 0 which is incorrect.

2. ## Re: Countifs where a column doe not equal x

Have you tried like this

=COUNTIFS(Data!\$H:\$H,'All Dates'!\$B3,Data!\$I:\$I,'All Dates'!\$C\$2,Data!\$F:\$F,'Month select 1'!C16,Data!J:J,"<>" & "Voided")

3. ## Re: Countifs where a column doe not equal x

Does this...

=COUNTIFS(Data!\$H:\$H,'All Dates'!\$B3,Data!\$I:\$I,'All Dates'!\$C\$2,Data!\$F:\$F,'Month select 1'!C16,Data!J:J,"<>*Void*")

succeed as intented?

4. ## Re: Countifs where a column doe not equal x

Originally Posted by magpie2000k
Hi All I have read and read and tried and tried but cant get formula to work.

Basically the below is the written formula

=COUNTIFS(Data!\$H:\$H,'All Dates'!\$B3,Data!\$I:\$I,'All Dates'!\$C\$2,Data!\$F:\$F,'Month select 1'!C16,Data!J:J,"<>Voided")

It all works but I need to exclude from the count Anywhere in column DATA!J:J where the entry is Voided or Possible Void

5. ## Re: Countifs where a column doe not equal x

Does this...

=COUNTIFS(Data!\$H:\$H,'All Dates'!\$B3,Data!\$I:\$I,'All Dates'!\$C\$2,Data!\$F:\$F,'Month select 1'!C16,Data!J:J,"<>*Void*")

succeed as intented?
Hi No it returns Zeros in every instance. which is clearly wrong

It is doing my head in as I cant understand why

6. ## Re: Countifs where a column doe not equal x

Originally Posted by Momentman
Have you tried like this

=COUNTIFS(Data!\$H:\$H,'All Dates'!\$B3,Data!\$I:\$I,'All Dates'!\$C\$2,Data!\$F:\$F,'Month select 1'!C16,Data!J:J,"<>" & "Voided")
Both options return a column full of zeros

7. ## Re: Countifs where a column doe not equal x

Originally Posted by magpie2000k
Both options return a column full of zeros
The below is a sample of the data in Column J:J

 Quote Created Sale Completed New Quote Created Quote Created Quote Created Sale Completed New Possible Void Quote Created Quote Created Quote Created Quote Created Quote Created Live Possible Void Quote Created Quote Created New Quote Created Live Quote Created Quote Created Quote Created Quote Created Quote Created Quote Created Sale Completed Possible Void Quote Created Quote Created Live Quote Created Quote Created Find Product New Sale Completed Live Quote Created Quote Created Live Possible Void Sale Completed Sale Completed Live New New Sale Completed Quote Created Quote Created New Quote Created New Live Possible Void Quote Created New Quote Created New Quote Cancelled Quote Created Quote Created Sale Completed Quote Created Quote Created Quote Created Quote Created Quote Created Quote Created Quote Created Possible Void New Voided Sale Completed Possible Void Possible Void Live New Live Quote Created

8. ## Re: Countifs where a column doe not equal x

Originally Posted by magpie2000k
Hi No it returns Zeros in every instance. which is clearly wrong

It is doing my head in as I cant understand why
What is the result of:

=COUNTIF(Data!J:J,"<>*Void*")

9. ## Re: Countifs where a column doe not equal x

What is the result of:

=COUNTIF(Data!J:J,"<>*Void*")
 If I go down to row 74 =COUNTIF(Data!J2:J74,"<>*Void*") it returns 66 which is correct

10. ## Re: Countifs where a column doe not equal x

Originally Posted by magpie2000k
 If I go down to row 74 =COUNTIF(Data!J2:J74,"<>*Void*") it returns 66 which is correct
This result might mean that the other one or more conditions (in red) do not hold or incorrectly specified by you...

=COUNTIFS(Data!\$H:\$H,'All Dates'!\$B3,Data!\$I:\$I,'All Dates'!\$C\$2,Data!\$F:\$F,'Month select 1'!C16,Data!J:J,"<>*Void*")

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•