### Need to download code?

View our list of code downloads.

### Navigation

 Wrox Programmer Forums Count rows which meet three conditions ???
 User Name Remember Me? Password Reminder Password Register
 | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
 Excel VBA Discuss using VBA for Excel programming.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
 Thread Tools Search this Thread Display Modes
#1 (permalink)
February 24th, 2009, 09:19 AM
 Registered User Join Date: Feb 2009 Posts: 2 Thanks: 0 Thanked 0 Times in 0 Posts
Count rows which meet three conditions ???

Hi everyone,

On my worksheet I will only count the rows which meet three conditions:
• The values in column ‘U’:< 45
• The values in column ‘U’:>135
• The values in column ‘V’ may not be 0 (<> 0)

Using the COUNTIF function I am able to get the results of each condition separately. (=COUNTIF(U3:U64000; ">135"))
However, combining all the conditions for the same row will require some VBA I guess.

Can someone provide me a way how to do this?

Thanks, Aad.
#2 (permalink)
February 24th, 2009, 10:30 PM
Friend of Wrox
 Points: 3,060, Level: 23
 Activity: 0%

Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts

You can try something similar to the one shown below

Code:
```
Public Function CntCriteria()
Dim ocell As Range
Dim iCnt As Long
For i1 = 1 To Range("U:U").Cells.SpecialCells(xlCellTypeLastCell).Row
Set ocell = Cells(i1, 21)
If ((ocell.Value < 45 Or ocell.Value > 135) And ocell.Offset(0, 1).Value <> 0) Then
iCnt = iCnt + 1
End If
Next
CntCriteria = iCnt
End Function```
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
#3 (permalink)
February 26th, 2009, 03:51 AM
 Registered User Join Date: Jan 2009 Location: California Posts: 8 Thanks: 0 Thanked 2 Times in 2 Posts

There are at least two ways to do this without VBA:
METHOD 1
requires an additional column, e.g. W. Each cell in the W column range contains the formula:
=IF(Vr<>0,Ur,"") where r = the row number.
The cell to contain the result (e.g. X1) uses the formula:
=COUNTIF(Wr:Wr+n,"<45")+COUNTIF(Wr:Wr+n,">135") where r is the 1st row number and n is the number of rows in evaluated range. {alternate: =COUNTIF(W:W,"<45")+COUNTIF(W:W,">135")}

METHOD TWO

uses the DCOUNT function. The formula in cell X1 is:
=DCOUNT(U4:V12,"Ucol",U1:V2)+DCOUNT(U4:V23,"Ucol", V1:W2)
The data names “Ucol” and “Vcol” can be anything you like, but they have to match as shown.

If the “Ucol” conditions had conjoined rather than disjoined (e.g. >45 and <135) the formula would have been simpler:
=DCOUNT(U4:V12,"Ucol",U1:W2)
It's difficult to make clear in this format, and I don't have rights to post HTML here, so a sample spreadsheet (xl2000) is available at:

http://click.officeliveemail.com/?ju=fe5912767263067a7517&ls=fdef1672736d0275771576 73&m=fef012797d6206&l=fec51c7677610478&s=fe1a12727 c600378731d75&jb=ff62127775&t=

for anyone interested.
#4 (permalink)
February 26th, 2009, 04:39 AM
 Registered User Join Date: Feb 2009 Posts: 2 Thanks: 0 Thanked 0 Times in 0 Posts
Count rows which meet three conditions ???

Thanks for responding,

The four worksheets with data contains 50k rows each while another worksheet calculates the results.

I ended up with a nested function which causes Excel to act very slow and it even crashed several times.

Here it is:
Code:
```
=SUMPRODUCT(INDEX((('2007'!\$U\$3:\$U\$64000<25)+('2007'!\$U\$3:\$U\$64000>155))*('2007'!\$V\$3:\$V\$64000<>0)*(MATCH('2007'!\$J\$3:\$J\$64000&""; '2007'!\$J\$3:\$J\$64000   &"";0)=ROW('2007'!\$J\$3:\$J\$64000)-ROW('2007'!\$J\$3)+1);0))```
But... finally I got my results.

For future projects I think it's more efficient to use VB when handling such amount of data.
As an alternative, I could save the data as .csv then import in Access and build some SQL queries to get the results I want.
(I'm more familiar with SQL as with worksheet functions and VBA)

Thanks again,

Ronald Ortelee
#5 (permalink)
February 26th, 2009, 10:02 AM
 Registered User Join Date: Jan 2009 Location: California Posts: 8 Thanks: 0 Thanked 2 Times in 2 Posts

Clever solution!

Excel has always been pokey with large amounts of data. Still, DCOUNT might calculate faster.

It would be interesting to time results of SUMPRODUCT/INDEX/MATCH vs. DCOUNT vs. VBA solution suggested by Shasur.

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is OffTrackbacks are Off Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Display count of rows debjanib ASP.NET 1.0 and 1.1 Professional 7 May 5th, 2006 04:13 AM retrieve everything AND count rows in one hit crmpicco MySQL 5 January 20th, 2006 06:02 AM count of rows ashokparchuri ADO.NET 3 April 19th, 2005 11:19 AM count the number of rows in the table crmpicco Javascript How-To 4 February 2nd, 2005 12:58 AM count child table rows melvik C# 6 January 25th, 2004 01:39 AM

All times are GMT -4. The time now is 02:23 PM.

 Contact Us - Wrox - Privacy Statement - Top

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.