COUNTIFS Not equal to operator

[Lambrix]

Good afternoon,

I am having trouble with not equal to operator, "<>". I am trying to count all entries that equal "10A3" in column P and all entries that are not equal to "8888" in column K. The correct answer should be 1, instead it is giving me the total number which is 11. Here is the formula I am using:

=COUNTIFS('Data'!$P:$P,"10A3",'Data'!$K:$K,"<>8888")

Any ideas? Thank you in advance for your help.

 

[VoG]

Try

=COUNTIFS('Data'!$P:$P,"10A3",'Data'!$K:$K,"<>"&8888)

 

[Lambrix]

I tried it but it didn't work. It came back with the same value: 11

=COUNTIFS('Data'!$P:$P,"10A3",'Data'!$K:$K,"<>"&8888)

 

[TheCman81]

Try this:

=COUNTIFS(Data!$P$2:$P$11,"10A3",Data!$K$2:$K$11,"<>8888")

 

[Marcelo Branco]

Try

=SUMPRODUCT(--(P1:P1000="10A3"),--(K1:K1000<>"8888"))

Adjust the range

M.

 

[Churchy LaFemme]

I am having the same problem. SUMPRODUCT does not seem to be the go-to.

As a test, I used this formula on the same worksheet: =COUNTIFS(R15:R20,"Pig",S15:S20,"Dog",T15:T20,"<>goon")
The formula also works for some criteria sets.

This worked fine. However, on the datea I am trying to count, I am getting nowhere. It does work for some criteria. I have delated leading, trailing, and excessive spaces. I have made sure the formatting for all my columns is the same. (That is, that column B, Column G, and Column H are the same format from top to bottom. Not that all three columns are the same.)

=COUNTIFS('FY12'!$B$11:$B$24,"Ordered",'FY12'!$G$11:$G$24,"Storage",'FY12'!$H$11:$H$24,"<>Dated")

By raw math, I should get 4. The formula returns 1.

Also tried this thread -http://www.mrexcel.com/forum/showth...ing-equal-to-or-greater-than-a-date-in-a-cell

If you don't see anything screamingly wrong with the fromula, my issue may be with the formatting in the columns. (This is not my workbook.)

Anyone have any ideas?

(Edited to fix link)

 

[Aladin Akyurek]

I am having the same problem. SUMPRODUCT does not seem to be the go-to.

As a test, I used this formula on the same worksheet: =COUNTIFS(R15:R20,"Pig",S15:S20,"Dog",T15:T20,"<>goon")
The formula also works for some criteria sets.

This worked fine. However, on the datea I am trying to count, I am getting nowhere. It does work for some criteria. I have delated leading, trailing, and excessive spaces. I have made sure the formatting for all my columns is the same. (That is, that column B, Column G, and Column H are the same format from top to bottom. Not that all three columns are the same.)

=COUNTIFS('FY12'!$B$11:$B$24,"Ordered",'FY12'!$G$11:$G$24,"Storage",'FY12'!$H$11:$H$24,"<>Dated")

By raw math, I should get 4. The formula returns 1.

Also tried this thread -http://www.mrexcel.com/forum/showth...ing-equal-to-or-greater-than-a-date-in-a-cell

If you don't see anything screamingly wrong with the fromula, my issue may be with the formatting in the columns. (This is not my workbook.)

Anyone have any ideas?

(Edited to fix link)

The formula is syntactically correct. You might want to run the TrimAll macro or ASAP Utilities on the relevant ranges in order to remove unwanted possible non-printable chars...

 

[Churchy LaFemme]

Another wrinkle, as I work through the rest of sheet, it's seems that when the sometimes, but not always, when the countif criteria includes something with "Dated" that should be excluded, the count is wrong.

 

[Churchy LaFemme]

Sorry if this is a double-post.

I had run ASAP.

Eventually, I removed the filters on the uncountable data sheet and then re-entered my formulas. That worked. So far. Although the counted data is not changing (not that data that was unco-operative) I plan to re-check the counts again later after I've closed and re-opened, just in case.

This one was a puzzler.

Thanks for the reality check on my syntax!