Formulas : Conditional Counting Data Rows

mramazan · Apr 1st 2004

I want to count number of rows which satisfy the conditions on two columns.

My Excell worksheet has data in 3 columns (A, B, C) and 20 rows.

First row is header/label row i.e. Name, City, Age .

Other rows contains data for several persons.

Now, I want to make summary of this database based upon the following condions (for example):

- count number of rows in column A if data in range A1:A20 contains/equal to "specific-name" AND data in range B1:B20 contains/equal to "specific-city"

I want to enter this "specific-name" in Cell E1 and "specific-city" in Cell E2. I want to get result in Cell E3.

Can anyone help me making this formula.

Thanks,
Ramazan

Anonymous · Apr 1st 2004

The SUMPRODUCT will do this.

=SUMPRODUCT((A1:A20="Name")*((B1:B20="CityName"))

Note: using only the logic checks makes each row binary and it therefore functions as a COUNTIF with multiple conditions.

Javy Dreamer · Apr 1st 2004

I tried using countif but couldn't pull it off, so I headed to VBA and here's what i got.
<pre>
Put the following in a VBA Module:

Function getMatch(cond1 As Range, cond2 As Range, val1 As Range, val2 As Range)
Dim i As Integer, result As Integer
result = 0
For i = 1 To val1.Cells.Count
If val1.Cells(i).Value = cond1.Value And val2.Cells(i).Value = cond2.Value Then
result = result + 1
End If
Next i
getMatch = result
End Function

And the following in the cell where u'll have the results displayed:

getMatch(E1, E2, A2:A21,B2:B21)

This is my first code as an answer but i guess it's right. Of course any of the Guru's can give u a cleaner option.
</pre>

Anonymous · Apr 1st 2004

Here are a couple of other threads (there are more here on OzGrid if you use the SEARCH link above for SUMPRODUCT) that might help you understand the SUMPRODUCT function and its uses to get a COUNT or a SUM.

http://www.ozgrid.com/forum/viewthread.php?tid=9623

http://www.ozgrid.com/forum/viewthread.php?tid=9599

http://www.ozgrid.com/forum/viewthread.php?tid=9379

mramazan · Apr 2nd 2004

Dear Tom & Javy Dreamer,

Many thanks for your quick replies. As advised by you, my problem has been solved by using SUMPRODUCT function.

Also thanks for guiding me regarding VBA module but at this stage, I am not much familiar with VBA programming (I can just record & edit SIMPLE macros) but now I am seriously thinking over learning it.

I am a new member on this forum. I searched it through google (to solve my problem) and now I have found the forum members very cooperative and helpful. I will continue visiting this forum for my learning (may be only as a reader).

Once again, thanks to every member who is helping others.

Regards,
Ramazan

mramazan · May 4th 2004

Quote

Originally posted by mramazan
I am not much familiar with VBA programming (I can just record & edit SIMPLE macros) but now I am seriously thinking over learning it.

Thanks guys for your help. Now, I have started taking interest in VBA.

So, the solution provided by Javy Dreamer worked well and solved purpose But I have more than 100 different cell in which I will use this function by changing arguments but my first argument E1 which is a range B3 is common at all places.

I want to do somethis like this:

Code

Public cond1 As Range Public cond1 As Range
cond1 = Range("B3").Value


Function getMatch(cond2 As Integer, val1 As Range, val2 As Range)
    Dim i As Integer, result As Long
    result = 0
    For i = 1 To val1.Cells.Count
        If val1.Cells(i).Value = cond1.Value And val2.Cells(i).Value = cond2 Then
            result = result + 1
        End If
    Next i
    getMatch = result
End Function

Display More

getMatch(E2, A2:A21,B2:B21)

But when I use this function, I get error message in VBA "invalid outside procedure"

Please guide to a new student of VBA.

Ramazan

Will Riley · May 4th 2004

Hi Ramazan,

Just a couple of tips.

Generally speaking, native XL functions will always be faster than user defined ones. Therefore you are nearly always better off using standard XL formulas unless absolutely necessary.

You could use SUMPRODUCT with a static range reference in the following way...

=SUMPRODUCT(($A$1:$A$20=$B$3)*($B$1:$B$20=E3))

copying the above formula down will always leave B3 as a static reference in the firts part of the formula, whereas E3 will become E4,E5,E6 as you copy down each row... The $ signs anchor the row & column references...

On a more cautious note, SUMPRODUCT can also have negative impacts on the calculation times of your workbook if used "in abundance" on very large ranges

You may be better off using a Pivot Table to summarize your data by region & city, etc... your data setup seems perfect for Pivot Tabling!

Anyway, welcome to Ozgrid.... glad you found us

Hope this helps

mramazan · May 4th 2004

Thanks for the reply. Simmilar SUMPRODUCT solution was suggested by Tom and I have implemented it in my project but now I am interesting in VBA solution (my interest for learning VBA has been increased !!!).

So, please provide me guidlines for using VBA. I will be much obliged.

Once again I will thank for all the generous contributors of this forum.

Will Riley · May 4th 2004

Use Javy Dreamer's function then - it works perfectly, other than the fact that it is a good deal slower than SUMPRODUCT

I understand your interest in VBA, however you should use it wisely - creating a function that is already available is wasting your time. Creating a function that is both already available & where your function is slower could also waste other users' time...

BTW, your code errors due to

Code

Public cond1 As Range Public cond1 As Range 
cond1 = Range("B3").Value

You cannot do this outside of a Sub or Function procedure, hence the error message,

Hope this helps,

Formulas : Conditional Counting Data Rows

Participate now!

Share