Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Carl
 
Posts: n/a
Default Countif Using Multiple Logic Tests

I am trying to use countif to count the number of times a number greater than
3 AND less than 10 occurs in a table. I can use countif for one criteria only
but cannot find a way to get it to work with more than one logic test. The
following formula does not work: COUNTIF(A1:A8,"and(3,<10)")
Thanks in advance,
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Countif Using Multiple Logic Tests

Use 2

=COUNTIF(A1:A8,"3")-COUNTIF(A1:A8,"=10")

or sumproduct

=SUMPRODUCT(--(A1:A83),--(A1:A8<10))

will return the same result

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Carl" wrote in message
...
I am trying to use countif to count the number of times a number greater
than
3 AND less than 10 occurs in a table. I can use countif for one criteria
only
but cannot find a way to get it to work with more than one logic test. The
following formula does not work: COUNTIF(A1:A8,"and(3,<10)")
Thanks in advance,



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Countif Using Multiple Logic Tests

Try something like this:

=SUMPRODUCT((A1:A83)*(A1:A8<10))

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

XL2002, WinXP


"Carl" wrote:

I am trying to use countif to count the number of times a number greater than
3 AND less than 10 occurs in a table. I can use countif for one criteria only
but cannot find a way to get it to work with more than one logic test. The
following formula does not work: COUNTIF(A1:A8,"and(3,<10)")
Thanks in advance,

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Carl
 
Posts: n/a
Default Countif Using Multiple Logic Tests

That solved the problem. Thanks!

"Peo Sjoblom" wrote:

Use 2

=COUNTIF(A1:A8,"3")-COUNTIF(A1:A8,"=10")

or sumproduct

=SUMPRODUCT(--(A1:A83),--(A1:A8<10))

will return the same result

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Carl" wrote in message
...
I am trying to use countif to count the number of times a number greater
than
3 AND less than 10 occurs in a table. I can use countif for one criteria
only
but cannot find a way to get it to work with more than one logic test. The
following formula does not work: COUNTIF(A1:A8,"and(3,<10)")
Thanks in advance,




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
Using COUNTIF in multiple wooksheets Savage Excel Discussion (Misc queries) 3 December 18th 05 09:20 PM
Countif on multiple dates Jim Excel Worksheet Functions 6 December 13th 05 06:56 PM
How do I COUNTIF in excel using multiple criteria? DJ_Swammi Excel Worksheet Functions 3 December 1st 05 05:16 PM
COUNTIF or SUM function (Multiple criteria) HELP!! Australia Excel Worksheet Functions 3 September 19th 05 07:39 AM
"countif" from multiple worksheets within workbook Excel Discussion (Misc queries) 2 December 10th 04 07:59 PM


All times are GMT +1. The time now is 10:41 AM.

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

About Us

"It's about Microsoft Excel"