[RESOLVED] Excel VBA - Count Occurances In Column

[Hack]

I have a column on a spreadsheet with a Title of "Action"

In this column, there will either be the word "Add" "Delete" or "Update"

As of the moment, the column goes from G:3 to G:731, but that will grow down.

I need to skip down two from the bottom (I know how to do this) and Count howmany times the word "Add", how many times the word "Delete", how many times the word "Update" appear as well as how many times there is nothing at all listed in this column.

I spent some time Googling =COUNT but I can't find any examples that satisify what I need to do.

[NeedSomeAnswers]

Hi Hack,

i could do this with a macro, if that's any help ??

not sure of an actual excel function to do this.

[Hack]

i could do this with a macro, if that's any help ??

Sure...that works!

[NeedSomeAnswers]

Quick question,

when you say -

I need to skip down two from the bottom

does that mean if you range is from G:3 to G:731 you only want to count until G729 ?

and secondly where do you want to put the totals ?

i could just messagebox them in the example, and let you sort that bit if you want ?

[Hack]

Just messagebox them. I'll figure out where they should go.

[NeedSomeAnswers]

This should do it,

you need to provide it with the last cell of the range. I cant remember how to make it figure this out for you when there are blanks in the column off the top of my head, i think i have some code at home though if you really need it ? Let me know !

Sub GetAction()
 
Dim RANGEBOTTOM As String
Dim cell
Dim strAction As String
Dim intAdd As Integer
Dim intUpdate As Integer
Dim intDelete As Integer
Dim intBlank As Integer
 
RANGEBOTTOM = "G731"
 
For Each cell In Range("G3:" & RANGEBOTTOM)
 
    strAction = cell.Value
    
    If UCase(strAction) = "ADD" Then
        intAdd = intAdd + 1
    ElseIf UCase(strAction) = "UPDATE" Then
        intUpdate = intUpdate + 1
    ElseIf UCase(strAction) = "DELETE" Then
        intDelete = intDelete + 1
    ElseIf strAction = "" Then
        intBlank = intBlank + 1
    End If
    
Next
 
MsgBox "Additions =" & CStr(intAdd)
MsgBox "Updates =" & CStr(intUpdate)
MsgBox "Deletions =" & CStr(intDelete)
MsgBox "blanks =" & CStr(intBlank)
 
End Sub

[Hack]

Thank you sir! I'll give it a shot.

[NeedSomeAnswers]

I have made a minor amendment which you might find useful, in this version it should count down to the bottom of your range for you ignoring blanks until it can find no more data, so in effect getting the bottom of your range for you.

edit - messed up the formatting in this post so posted it again below !!

[NeedSomeAnswers]

Sub GetAction()
 
Dim blnBottom As Boolean
Dim RANGEBOTTOM As String
Dim ACTUALRANGEBOTTOM As String
Dim cell
Dim strAction As String
Dim intAdd As Integer
Dim intUpdate As Integer
Dim intDelete As Integer
Dim intBlank As Integer
 
Dim strrange As String
Dim n As Integer
 
startrange = "G3"
 
n = 0
 
blnBottom = False
 
strrange = CStr(startrange) & ":" & "G5000"
 
Do Until blnBottom = True
 
ACTUALRANGEBOTTOM = RANGEBOTTOM
 
If n <> 0 Then
startrange = "G" & RANGEBOTTOM
strrange = CStr(startrange) & ":" & "G5000"
End If
 
RANGEBOTTOM = Sheet1.range(strrange).End(xlDown).Row
 
If RANGEBOTTOM = "1048576" Then
blnBottom = True
 
End If
 
n = n + 1
 
Loop
 
'RANGEBOTTOM = "G731"
 
For Each cell In range("G3:" & ACTUALRANGEBOTTOM)
 
strAction = cell.Value
 
If UCase(strAction) = "ADD" Then
intAdd = intAdd + 1
ElseIf UCase(strAction) = "UPDATE" Then
intUpdate = intUpdate + 1
ElseIf UCase(strAction) = "DELETE" Then
intDelete = intDelete + 1
ElseIf strAction = "" Then
intBlank = intBlank + 1
End If
 
Next
 
MsgBox "Additions =" & CStr(intAdd)
MsgBox "Updates =" & CStr(intUpdate)
MsgBox "Deletions =" & CStr(intDelete)
MsgBox "blanks =" & CStr(intBlank)
 
End Sub

[Siddharth Rout]

Hi Hack

Do you need to do it via code as you can achieve the same with a very simple native function called Countif()

for example

=COUNTIF(A1:A6,"Add")

[anhn]

@Hack, in case you still want to count in code, this should be quicker:

Sub ActionCount()
   Dim AddCount    As Long
   Dim DeleteCount As Long
   Dim UpdateCount As Long
   Dim BlankCount  As Long
   Dim OthersCount As Long
   Dim ActionRange As Range, r1 As Long, r2 As Long
   
   With Sheet1 '-- your data sheet
      r1 = 3
      r2 = .Cells(.Rows.Count, "G").End(xlUp).Row
      If r2 < r1 Then r2 = r1
      Set ActionRange = .Range("G" & r1 & ":G" & r2)
   End With
   
   With Application.WorksheetFunction
      AddCount = .CountIf(ActionRange, "Add")
      DeleteCount = .CountIf(ActionRange, "Delete")
      UpdateCount = .CountIf(ActionRange, "Update")
      BlankCount = .CountBlank(ActionRange)
   End With
   OthersCount = (r2 - r1 + 1) - (AddCount + DeleteCount + UpdateCount + BlankCount)
   MsgBox "Add" & vbTab & ":  " & AddCount & vbCrLf & _
          "Delete" & vbTab & ":  " & DeleteCount & vbCrLf & _
          "Update" & vbTab & ":  " & UpdateCount & vbCrLf & _
          "Blank" & vbTab & ":  " & BlankCount & vbCrLf & _
          "Others" & vbTab & ":  " & OthersCount & vbCrLf & _
          "––––––––––––––" & vbCrLf & _
          "Total" & vbTab & ":  " & (r2 - r1 + 1), _
          vbInformation, "Action Count"
End Sub

[NeedSomeAnswers]

Hi Ahn,

I particularly like your set ActionRange code -

Dim ActionRange As Range, r1 As Long, r2 As Long
   
   With Sheet1 '-- your data sheet
      r1 = 3
      r2 = .Cells(.Rows.Count, "G").End(xlUp).Row
      If r2 < r1 Then r2 = r1
      Set ActionRange = .Range("G" & r1 & ":G" & r2)
   End With

Haven't come across this before, and will be using that now in the future

Cheers !!

[anhn]

Hi Ahn,

I particularly like your set ActionRange code -

Dim ActionRange As Range, r1 As Long, r2 As Long
   
   With Sheet1 '-- your data sheet
      r1 = 3
      r2 = .Cells(.Rows.Count, "G").End(xlUp).Row
      If r2 < r1 Then r2 = r1
      Set ActionRange = .Range("G" & r1 & ":G" & r2)
   End With

Haven't come across this before, and will be using that now in the future

Cheers !!

By using Sheet.Rows.Count, it will work for both Excel-2007 and prior. You don't need to know how many rows a worksheet has.

Using BottomCell.End(xlUp) is better than using TopCell.End(xlDown), particularly when there are some blank cells in the middle.

[NeedSomeAnswers]

Yeh,

previously I have tried the xlDown method, but obviously as you said it's no good if you have blank cells, I hadn't thought about counting from the bottom upwards !!

My way was just an ugly work around.

[Hack]

While I like coding solutions, native functions work as well.

Thanks to all.

[anhn]

Is that resolved? Mark it.

[Hack]

Is that resolved? Mark it.

I am aware of the resolution requirements anhn.

[Hack]

What is the difference =COUNT(whatever) and =COUNTIF(Whatever)

I know that in my little spreadsheet, using COUNTIF returns the correct number where COUNT returns 1, but why is that?

If you need COUNTIF, then what is the purpose of COUNT?

[Siddharth Rout]

Hack:

COUNT() is used get the number of numeric entries.
Similarly COUNTA() is used get the count of Alphanumeric entries.

Countif() Counts the number of cells within a range that meet the given criteria.

For example see the pic

[anhn]

@Hack, everything you need to know about different kinds of counting functions is in Excel Help:

[Hack]

Thanks koolsid!

[Vinod Krishna.C]

Dear all,

Firstly, this post looks promising wrt the requirement I have.

Hoping that someone will hear me on this cause I'm reply to a post which is 5 years old.

Here's my requirement though:

I have a table called "Names" having the following details:

No.	Name
1	Anderson
1	Anderson
2	Blake
2	Blake
2	Blake
3	Conan

I would like the Output to be as follows:

No.	Count
1	2
2	3
3	1

Would love to have a generic VBA code to do this.

Any help or hint on this would be much appreciated.

Thanks,
Vinod Krishna