Count rows which meet three conditions ???

Aad · February 24th, 2009, 09:19 AM

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.

Shasur · February 24th, 2009, 10:30 PM

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

tomj54 · February 26th, 2009, 03:51 AM

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.

Aad · February 26th, 2009, 04:39 AM

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

tomj54 · February 26th, 2009, 10:02 AM

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.