Formulas : Conditional Counting Data Rows

  • 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

  • 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.

  • I tried using countif but couldn't pull it off, so I headed to VBA and here's what i got.
    <pre&gt;
    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&gt;

  • 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

  • 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:



    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

  • 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

  • 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.

  • 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
    1. Public cond1 As Range Public cond1 As Range
    2. cond1 = Range("B3").Value


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


    Hope this helps,