COUNTIF

[Roy Goddard]

Hi there

I want to use countif to count the numbers in a row that are 'greater than'
but 'less than'
something like =COUNTIF(P3:U3,">5", "<25") but can't get it to work.
I've read that an array formula will do the trick, but don't know where to
start!

Regards
Roy

 

[Paul]

Hi there

I want to use countif to count the numbers in a row that are 'greater than'
but 'less than'
something like =COUNTIF(P3:U3,">5", "<25") but can't get it to work.
I've read that an array formula will do the trick, but don't know where to
start!

Regards
Roy

COUNTIF (and SUMIF) can have only one criterion.
To get more, you use SUMPRODUCT.

The equivalent for COUNTIF is
=SUMPRODUCT((P3:U3>5)*(P3:U3<25))

The equivalent for SUMIF would be
=SUMPRODUCT((P3:U3>5)*(P3:U3<25)*P3:U3)

 

[Roy Goddard]

Thanks Paul, works great

COUNTIF (and SUMIF) can have only one criterion.
To get more, you use SUMPRODUCT.

The equivalent for COUNTIF is
=SUMPRODUCT((P3:U3>5)*(P3:U3<25))

The equivalent for SUMIF would be
=SUMPRODUCT((P3:U3>5)*(P3:U3<25)*P3:U3)

 

[Jon Barchenger[MS]]

Good morning Roy -

Here is an example of a formula that will do what you are wanting the countif formula to do.

=SUM(IF((P3:U3>5)*(P3:U3<25),1,0))

You will need to use the CTL + SFT + ENTER key combination to enter this as an array
formula and it will return a count of all of the numbers that are GREATER than 5 and LESS than 25.

Thanks,
Jon Barchenger
--------------------
| **From: "Roy Goddard" <[email protected]>
| **Newsgroups: microsoft.public.excel.worksheet.functions
| **Subject: COUNTIF
| **Lines: 12
| **X-Priority: 3
| **X-MSMail-Priority: Normal
| **X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
| **X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
| **Message-ID: <[email protected]>
| **Date: Thu, 30 Oct 2003 21:16:03 +1300
| **NNTP-Posting-Host: 210.54.69.33
| **X-Complaints-To: (e-mail address removed)
| **X-Trace: news.xtra.co.nz 1067501754 210.54.69.33 (Thu, 30 Oct 2003 21:15:54 NZDT)
| **NNTP-Posting-Date: Thu, 30 Oct 2003 21:15:54 NZDT
| **Organization: Xtra
| **Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!nntp-relay.ihug.net!newsfeeds.ihug.co.nz!ihug.co.nz!
news.xtra.co.nz!53ab2750!not-for-mail
| **Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.worksheet.functions:168136
| **X-Tomcat-NG: microsoft.public.excel.worksheet.functions
| **
| **Hi there
| **
| **I want to use countif to count the numbers in a row that are 'greater than'
| **but 'less than'
| **something like =COUNTIF(P3:U3,">5", "<25") but can't get it to work.
| **I've read that an array formula will do the trick, but don't know where to
| **start!
| **
| **Regards
| **Roy
| **
| **
| **