Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
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.
 
Old February 24th, 2009, 10:30 PM
Friend of Wrox
 
Join Date: Sep 2005
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)
 
Old February 26th, 2009, 03:51 AM
Registered User
 
Join Date: Jan 2009
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.
 
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
 
Old February 26th, 2009, 10:02 AM
Registered User
 
Join Date: Jan 2009
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.





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





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