Countifs for if not equal to "Exception"

[Cakz Primz]

Dear all,

I need to count based on 3 criteria, in a userform:

1. based on system number = TextBox1.Value
2. based on status "Active"
3. based on remarks, if not equal to "Exception"

with this code below:

TextBox5.Value = Application.WorksheetFunction.CountIfs(Sheets("PLREP").Range("B8:B150000"), TextBox1.Value, Sheets("PLREP").Range("G8:G150000"), "Active") - Application.WorksheetFunction.CountIfs(Sheets("PLREP").Range("B8:B150000"), TextBox1.Value, Sheets("PLREP").Range("G8:G150000"), "Active")

it gave me the result = 5, but the correct result should be 4, as we have 1 Exception

And when I am trying to use this code below, it gave me an error, in "Exception":

TextBox5.Value = Application.WorksheetFunction.CountIfs(Sheets("PLREP").Range("B8:B150000"), TextBox1.Value, Sheets("PLREP").Range("G8:G150000"), "Active") - Application.WorksheetFunction.CountIfs(Sheets("PLREP").Range("B8:B150000"), TextBox1.Value, Sheets("PLREP").Range("G8:G150000"), "Active", Sheets("PLREP").Range("AQ8:AQ150000"), <>"Exception")

How is the correct syntax to count if not equal to "Exception"?

Thanks in advance.
Prima Indonesia

 

[Joe4]

Try changing this condition:

<>"Exception"

to this:

"<>Exception"

 

[Cakz Primz]

D

Try changing this condition:

<>"Exception"

to this:

"<>Exception"

Dear Joe4,

Thanks for your help and assistance, the code is working very well.
Thank so much.
Problem solved.

Regards,
Prima Indonesia

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Joe4]

You are welcome.
Glad I was able to help!