COUNTIF - Multiple Criteria
 

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")

COUNTIF - Multiple Criteria
 

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

--
HTH

Bob Phillips

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

"Uggywuggy" wrote in message
...
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")

COUNTIF - Multiple Criteria
 

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


"Uggywuggy" wrote:

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")

COUNTIF - Multiple Criteria
 

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 ?



"Ron Coderre" wrote:

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


"Uggywuggy" wrote:

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")

COUNTIF - Multiple Criteria
 

Uggywuggy

RegardingL
if I want to count all rows but EXCLUDE those where the values are New/Closed/Monitor <<

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


"Uggywuggy" wrote:

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 ?



"Ron Coderre" wrote:

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


"Uggywuggy" wrote:

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")

COUNTIF - Multiple Criteria
 

Ron

Many Thanks. Will add then subtract

Cheers



"Ron Coderre" wrote:

Uggywuggy

RegardingL
if I want to count all rows but EXCLUDE those where the values are New/Closed/Monitor <<

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


"Uggywuggy" wrote:

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 ?



"Ron Coderre" wrote:

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


"Uggywuggy" wrote:

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")

COUNTIF - Multiple Criteria
 

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

--
HTH

Bob Phillips

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

"Uggywuggy" wrote in message
...
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 ?



"Ron Coderre" wrote:

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


"Uggywuggy" wrote:

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")