I need a formula that counts how many entries there are that DONT meet a certain criteria

[VivatMartin]

Hi All

I am looking for a formula to count how many entries there are on a spreadsheet, that DO NOT meet a criteria, for example, I want it to count everything that dos NOT have "Closed" in the column F

Hoping that you may be able to provide me with an example that will save me having to manually count about 1.4k entries!

Thanks
Martin

[Andrew-R]

If your range was, say, F1:F100 then something like:

=COUNTIF(F1:F100,"<>Closed")

Perhaps.

[Pete_UK]

Entries in one column?

Pete

EDIT: I was going to suggest:

=COUNTIF(F1:F100,"<>*Closed*")

[VivatMartin]

Many Thanks to you both, solved my problem wonderfully

[VivatMartin]

Actually, thinking about it would it be possible to make it ignore two criteria, i.e. Closed and Accounts Not Required - Many thanks, and sorry for the mistake!

[Andrew-R]

Are you really using Excel 2000, or do you have a more up to date version? I makes a difference to how easy this is.

[VivatMartin]

Are you really using Excel 2000, or do you have a more up to date version? I makes a difference to how easy this is.

Yes, I am REALLY using Excel 2000, that's all that my work has to offer I am afraid. I must apologise

[Andrew-R]

OK, still working with F1:F100, this should do the job:

=SUMPRODUCT(--(F1:F100<>"Closed"),--(F1:F100<>"Accounts Not Required"))

[VivatMartin]

OK, still working with F1:F100, this should do the job:

=SUMPRODUCT(--(F1:F100<>"Closed"),--(F1:F100<>"Accounts Not Required"))

Thank you very much, once again!