Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 24th, 2009, 09:19 AM
Aad Aad is offline
Registered User
 
Join Date: Feb 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Question 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.
Reply With Quote
  #2 (permalink)  
Old February 24th, 2009, 10:30 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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)
Reply With Quote
  #3 (permalink)  
Old February 26th, 2009, 03:51 AM
Registered User
 
Join Date: Jan 2009
Location: California
Posts: 8
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old February 26th, 2009, 04:39 AM
Aad Aad is offline
Registered User
 
Join Date: Feb 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
  #5 (permalink)  
Old February 26th, 2009, 10:02 AM
Registered User
 
Join Date: Jan 2009
Location: California
Posts: 8
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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.


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