| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use multiple criteria with COUNTIF: between dates and not blank | Excel Worksheet Functions | |||
| Counting cells using multiple criteria | Excel Worksheet Functions | |||
| Multiple Criteria using countif | Excel Worksheet Functions | |||
| multiple criteria in one field 4a,4b etc of countif? | Excel Discussion (Misc queries) | |||
| Multiple CountIf Criteria | Excel Worksheet Functions | |||
Linear Mode
