COUNTIF - Multiple Criteria

[Guest]

I'm trying to count the number of rows where a cell DOESN't equal a number of
values. I know this isn't the syntax but am looking for something as follows;

i.e. COUNTIF(A1:A100,"<>NEW", "<>CLOSED", "<>MONITOR")

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A100={"NEW","CLOSED","MONITOR"}))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

If you want to also skip blanks, try this:
=COUNTA(A1:A100)-SUM(COUNTIF(A1:A100,{"NEW","CLOSED","MONITOR"}))

Otherwise, to include blanks in the count:
=ROWS(A1:A100)-SUM(COUNTIF(A1:A100,{"NEW","CLOSED","MONITOR"}))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ron/Bob

Ta. And if I want to count all rows but EXCLUDE those where the values are
New/Closed/Monitor can I use the '<>' symbols ?

 

[Guest]

Uggywuggy

RegardingL
Both formulas that I posted achieve that....and no, you can't use <> in this
application. Each test in the formula is independent of the other tests.

Example:
If cells A1:A10 contain 1 instance each of New/Closed/Monitor, this part of
the formula:
SUM(COUNTIF(A1:A100,{"NEW","CLOSED","MONITOR"}))

returns 3.

Changing it to:
SUM(COUNTIF(A1:A100,{"<>NEW","<>CLOSED","<>MONITOR"}))

returns 27.

Consequently, you need to count ALL cells and subtract the cells matching
New/Closed/Monitor.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ron

Many Thanks. Will add then subtract

Cheers

 

[Bob Phillips]

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(A1:A100,{"NEW","CLOSED","MONITOR"},0)))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)