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