Wrox Programmer Forums Count rows which meet three conditions ???
 |
 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

February 24th, 2009, 09:19 AM
 Aad 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?

February 24th, 2009, 10:30 PM
 Shasur Friend of Wrox Join Date: Sep 2005 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)

February 26th, 2009, 03:51 AM
 tomj54 Registered User Join Date: Jan 2009 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.

February 26th, 2009, 04:39 AM
 Aad 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

February 26th, 2009, 10:02 AM
 tomj54 Registered User Join Date: Jan 2009 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.

 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