Answered by:
Count if in excel by vba
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*")
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 RangelngCount = 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 rngToFindCountSpecial = 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 referencelngRef = 3 'These values used to test
strToCount = "apple" 'These values used to testWith Sheets("Sheet1") 'Edit "Sheet1" to your sheet name
Set rngNumbers = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End WithMsgBox CountSpecial(rngNumbers, lngRef, strToCount)
End SubExample 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

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
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 RangelngCount = 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 rngToFindCountSpecial = 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 referencelngRef = 3 'These values used to test
strToCount = "apple" 'These values used to testWith Sheets("Sheet1") 'Edit "Sheet1" to your sheet name
Set rngNumbers = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End WithMsgBox CountSpecial(rngNumbers, lngRef, strToCount)
End SubExample 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


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 testIn 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 810 hrs before I can reply again.
Regards, OssieMac 
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 testSet 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.

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
