Countif <> value

[Cossie]

Hi All

I need to count a range of cells whose value does not equal "a", "b", "c", "d", or "x"

need help with the syntax please

Many thanks as usual

 

[alvin-chung]

Assume your range is A1:A10, try

=COUNTIFS(A1:A10,"<>a",A1:A10,"<>b",A1:A10,"<>c",A1:A10,"<>d",A1:A10,"<>x")

 

[Aladin Akyurek]

Hi All

I need to count a range of cells whose value does not equal "a", "b", "c", "d", or "x"

need help with the syntax please

Many thanks as usual

Perhaps:

=COUNTIF(A2:A100,"<>"&{"","a", "b", "c", "d", "x"})

 

[Cossie]

Thanks Aladin

is it possible to add sumproduct to this or would it be better to use index and match or something else?

thanks very much

 

[Aladin Akyurek]

Thanks Aladin

is it possible to add sumproduct to this or would it be better to use index and match or something else?

thanks very much

No, why? CountIf is faster than SumProduct. And Index/Match doesn't count; it fetches/retrieves an already existing single value.