none
Count if in excel by vba RRS feed

  • Question

  • I had following example

    how can i count the number for column B

    like row no1 gt apple. then apple will add 1.

    I only able to find coding like below:

    Dim x As Long
    x = Range("D" & Rows.Count).End(xlUp).Row
    If x < 12 Then x = 12
    Range("C2") = Application.WorksheetFunction.CountIf(Range("B12:B" & x), "*apple*")

    Thursday, January 5, 2012 4:02 AM

Answers

  • Am I correct in assuming that the cells with the 3 and 8 are merged cells? If so, try the following UDF (User Defined Function)

    Function CountSpecial(rng As Range, lngR As Long, strC As String)
    Dim rngToFind As Range
    Dim rngMrgArea As Range
    Dim lngCount As Long
    Dim cel As Range

    lngCount = 0    'Initialize to zero

    For Each rngToFind In rng
      If rngToFind.Value = lngR Then
        'In the following line, if rngToFind is not in _
         a merged range then it remains as just one cell.
        Set rngMrgArea = rngToFind.MergeArea
       
        For Each cel In rngMrgArea
          If cel.Offset(0, 1) = strC Then
            lngCount = lngCount + 1
          End If
        Next cel
      End If
    Next rngToFind

    CountSpecial = lngCount

    End Function

    Example of calling the function from VBA.

    Sub TestFunction()
    Dim rngNumbers As Range   'Range containing the number references
    Dim lngRef As Long        'Number reference
    Dim strToCount As String  'String to match and count against number reference

    lngRef = 3        'These values used to test
    strToCount = "apple"  'These values used to test

    With Sheets("Sheet1")   'Edit "Sheet1" to your sheet name
      Set rngNumbers = .Range(.Cells(2, "A"), _
          .Cells(.Rows.Count, "A").End(xlUp))
    End With

    MsgBox CountSpecial(rngNumbers, lngRef, strToCount)
    End Sub

    Example of calling the function on a worksheet.

    =countspecial(A2:A20,5,"apple")

     


    Regards, OssieMac
    • Marked as answer by brownie yeo Tuesday, January 10, 2012 12:01 AM
    Thursday, January 5, 2012 7:25 AM
  • The function has been written so that you can enter 3 parameters. The range of cells containing the numbers, one of the specific numbers and the name of fruit to count against that number.

    In the example that you posted the range of numbers was in A2:A14.

    The example of code below sets a variable named rngNumbers from A2 to the last used cell in column A so that the range of numbers is dynamic and just so long as it starts at A2, it does not matter what row it ends on.

    .Range(.Cells(2, "A) means that it starts at row 2 in column A.

    .Cells(.Rows.Count, "A") means the cell at maximum number of rows in column A

    .End(xlUp) is like as if you placed the cursor on the last row on the worksheet in column A and then held the Ctrl key and pressed the Up arrow then the cursor would move up and stop at the last used cell in column A.

    Set rngNumbers = .Range(.Cells(2, "A"), _
          .Cells(.Rows.Count, "A").End(xlUp))

    Now the function that I wrote requires you to tell it the range that contains the numbers, the specific number to search for in that range and what type of fruit to count against that number in the adjacent column to the right.

    Now if you have entered the data exactly as per your example in your initial question, copied the Function CountSpecial and the Sub TestFunction into the standard module, if you run the sub TestFunction then it will return the number of instances of apple against the 3 and the answer is 1.

    If you alter the strToCount = "apple" to "orange" then it should return 2.

    So you can alter either lngRef value or strToCount value. The range is entered in the code above and if the range of numbers does not start at cell A2 then you need to change the column Id to the column with the numbers. Both column Id's in the line of code will be the same because there is only one column of numbers.

    You can also enter the formula in a cell on the worksheet like the following.

    =countspecial(A2:A14,3,"orange")

    Where A2:A14 contains the column of numbers.

    3 is the number to search for

    "apple" is the fruit to count against the number


    Regards, OssieMac
    • Marked as answer by brownie yeo Tuesday, January 10, 2012 12:01 AM
    Friday, January 6, 2012 5:09 AM

All replies

  • Am I correct in assuming that the cells with the 3 and 8 are merged cells? If so, try the following UDF (User Defined Function)

    Function CountSpecial(rng As Range, lngR As Long, strC As String)
    Dim rngToFind As Range
    Dim rngMrgArea As Range
    Dim lngCount As Long
    Dim cel As Range

    lngCount = 0    'Initialize to zero

    For Each rngToFind In rng
      If rngToFind.Value = lngR Then
        'In the following line, if rngToFind is not in _
         a merged range then it remains as just one cell.
        Set rngMrgArea = rngToFind.MergeArea
       
        For Each cel In rngMrgArea
          If cel.Offset(0, 1) = strC Then
            lngCount = lngCount + 1
          End If
        Next cel
      End If
    Next rngToFind

    CountSpecial = lngCount

    End Function

    Example of calling the function from VBA.

    Sub TestFunction()
    Dim rngNumbers As Range   'Range containing the number references
    Dim lngRef As Long        'Number reference
    Dim strToCount As String  'String to match and count against number reference

    lngRef = 3        'These values used to test
    strToCount = "apple"  'These values used to test

    With Sheets("Sheet1")   'Edit "Sheet1" to your sheet name
      Set rngNumbers = .Range(.Cells(2, "A"), _
          .Cells(.Rows.Count, "A").End(xlUp))
    End With

    MsgBox CountSpecial(rngNumbers, lngRef, strToCount)
    End Sub

    Example of calling the function on a worksheet.

    =countspecial(A2:A20,5,"apple")

     


    Regards, OssieMac
    • Marked as answer by brownie yeo Tuesday, January 10, 2012 12:01 AM
    Thursday, January 5, 2012 7:25 AM
  • Hi OssieMac.

    Thnx for ur reply.

    Yes.3 and 8 is merge cell.

    Ur code VBA didnt have response..

    Dont have CountSpecial function at msgbox there..

    Thursday, January 5, 2012 8:11 AM
  • Dont have CountSpecial function at msgbox there..


    I don't understand what you mean by your comment. Did you get an error message or did it simply return zero or what occurred? I suppose you did copy the Function CountSpecial code into a standard module?

    The function was tested using a copy of the data that you posted with the numbers in column A and the fruit names in column B.

    The following 2 lines in the TestFunction sub are required for the test. In your production code, you will need to edit the code to include the parameters for these.

    lngRef = 3        'These values used to test
    strToCount = "apple"  'These values used to test

    In your test data are your numbers in column A and the fruit names in column B? If not, edit both instances of "A" in the following line of the TestFunction sub to the required column with the numbers otherwise you are testing from the incorrect range.

    Set rngNumbers = .Range(.Cells(2, "A"), _
          .Cells(.Rows.Count, "A").End(xlUp))

    It will be another 8-10 hrs before I can reply again.

     


    Regards, OssieMac
    Thursday, January 5, 2012 11:01 AM
  • Hi. Sorry. I previously forget to put Function CountSpecial inside module. Dunno where should i put it.

    Now, i had put the function inside but dont understand ur coding.

    If according above example, is it i should put like this?

    lngRef = 10        'These values used to test
    strToCount = "apple"  'These values used to test

    Set rngNumbers = .Range(.Cells(2, "A"), _
          .Cells(.Rows.Count, "B").End(xlUp))

     

    Then it show me message box 1.

    Wat is tat mean?

    Please explain to me. I am new to vba. Thnx.

    Friday, January 6, 2012 3:44 AM
  • The function has been written so that you can enter 3 parameters. The range of cells containing the numbers, one of the specific numbers and the name of fruit to count against that number.

    In the example that you posted the range of numbers was in A2:A14.

    The example of code below sets a variable named rngNumbers from A2 to the last used cell in column A so that the range of numbers is dynamic and just so long as it starts at A2, it does not matter what row it ends on.

    .Range(.Cells(2, "A) means that it starts at row 2 in column A.

    .Cells(.Rows.Count, "A") means the cell at maximum number of rows in column A

    .End(xlUp) is like as if you placed the cursor on the last row on the worksheet in column A and then held the Ctrl key and pressed the Up arrow then the cursor would move up and stop at the last used cell in column A.

    Set rngNumbers = .Range(.Cells(2, "A"), _
          .Cells(.Rows.Count, "A").End(xlUp))

    Now the function that I wrote requires you to tell it the range that contains the numbers, the specific number to search for in that range and what type of fruit to count against that number in the adjacent column to the right.

    Now if you have entered the data exactly as per your example in your initial question, copied the Function CountSpecial and the Sub TestFunction into the standard module, if you run the sub TestFunction then it will return the number of instances of apple against the 3 and the answer is 1.

    If you alter the strToCount = "apple" to "orange" then it should return 2.

    So you can alter either lngRef value or strToCount value. The range is entered in the code above and if the range of numbers does not start at cell A2 then you need to change the column Id to the column with the numbers. Both column Id's in the line of code will be the same because there is only one column of numbers.

    You can also enter the formula in a cell on the worksheet like the following.

    =countspecial(A2:A14,3,"orange")

    Where A2:A14 contains the column of numbers.

    3 is the number to search for

    "apple" is the fruit to count against the number


    Regards, OssieMac
    • Marked as answer by brownie yeo Tuesday, January 10, 2012 12:01 AM
    Friday, January 6, 2012 5:09 AM
  • Thnx for ur explaination. OssieMac.

    Finally i understand it.

    SO if i want it auto calculate all total for the lngRef.

    Is it i should put rngNumbers to test?

    Means it can calculate there are 8 apples total if according my example.

    Friday, January 6, 2012 7:52 AM