Countifs where a column doe not equal x

[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

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.

Please help

 

[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")

 

[Aladin Akyurek]

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?

 

[Momentman]

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

Misread your request. I think the wildcard like aladin proposed is what you need

 

[magpie2000k]

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

 

[magpie2000k]

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

 

[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

<colgroup><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

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*")

 

[magpie2000k]

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

<colgroup><col width="213"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

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

<TBODY>
</TBODY>

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*")