Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 11th 06, 11:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3
Default 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   Report Post  
Old July 12th 06, 12:03 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,593
Default 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   Report Post  
Old July 12th 06, 12:10 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,118
Default 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   Report Post  
Old July 12th 06, 12:20 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3
Default 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   Report Post  
Old July 12th 06, 12:34 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,118
Default 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   Report Post  
Old July 12th 06, 01:00 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3
Default 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   Report Post  
Old July 12th 06, 09:11 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,593
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use multiple criteria with COUNTIF: between dates and not blank l.shields Excel Worksheet Functions 13 July 27th 09 06:53 PM
Counting cells using multiple criteria andrew.curley Excel Worksheet Functions 4 June 12th 06 04:41 PM
Multiple Criteria using countif calmo Excel Worksheet Functions 6 October 25th 05 08:33 PM
multiple criteria in one field 4a,4b etc of countif? Anne-Marie Excel Discussion (Misc queries) 1 October 20th 05 02:23 PM
Multiple CountIf Criteria JerryS Excel Worksheet Functions 1 October 14th 05 07:14 PM


All times are GMT +1. The time now is 12:55 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017