Help with counts formula

[p4nny]

Hi

I would like to count how many instances cells within column C do not equal a named range with a second criteria,

something like this:

=countifs(C:C,"<>"&NamedRange, D:D,"1")

Is this possible?

Many thanks

 

[Joe4]

Did you try it? It seems to work fine for me.
Is your NamedRange a single cell?

 

[p4nny]

Hi
Thanks for trying trying. My named range is in cells A1:A10.

I think the formula works when the NamedRange is a single cell.

 

[Joe4]

Thanks for trying trying. My named range is in cells A1:A10.

I think the formula works when the NamedRange is a single cell.

That is correct, because that is how COUNTIFS typically works - your criteria is a single value.

What exactly are you trying to do with a multi-range criteria? If it was equals, I would think that maybe you are trying to see if the value in column C is any one of those values.
But you are using "<>". So I am really not sure what it is that you are trying to do.

You may be better off to just post your data and explain what it is that you are trying to do.

 

[Aladin Akyurek]

Hi

I would like to count how many instances cells within column C do not equal a named range with a second criteria,

something like this:

=countifs(C:C,"<>"&NamedRange, D:D,"1")

Is this possible?

Many thanks

Either enter

=SUMPRODUCT(--ISNA(MATCH(NamedRange,$C$2:$C$2000,0)),--($D$2:$D$2000=1))

Or control+shift+enter

=SUM(IF(ISNA(MATCH(NamedRange,$C$2:$C$2000,0)),IF($D$2:$D$2000=1,1)))