COUNTIF

  • Thread starter Thread starter Roy Goddard
  • Start date Start date
R

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
 
Roy Goddard said:
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
Click to expand...

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)
 
Thanks Paul, works great


Paul said:
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)
Click to expand...
 
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
| **
| **
| **
 
Back
Top