Count Multiple Items in Same Column
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Count Multiple Items in Same Column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    491
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Count Multiple Items in Same Column

    Hi - How do I add another variable to this code? I want it to count how many times "Apple" or "Pear" appear in the column?


    Code:
    With Sheet1    Dim x As Long
       x = .Range("A" & Rows.Count).End(xlUp).Row
    
    
      Dim cnt As Integer
      cnt = 0
      For i = 1 To x
        If .Cells(i, 1).Value = "Apple" Then
          cnt = cnt + 1
        End If
      Next i
    
    
    
    
    .Range("C1").Value = cnt
    
    
    End With

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Count Multiple Items in Same Column

    Change your IF line to:
    Code:
    If .Cells(i, 1).Value = "Apple" Or .Cells(i, 1).Value = "Pear" Then
    Last edited by Joe4; Aug 13th, 2019 at 12:40 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,184
    Post Thanks / Like
    Mentioned
    87 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Count Multiple Items in Same Column

    This one line of code will do it for "Apple":
    Code:
    Sub test()
        Range("C1") = WorksheetFunction.CountIf(Range("A:A"), "Apple")
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Board Regular
    Join Date
    Oct 2009
    Posts
    491
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Multiple Items in Same Column

    @mumps I was using .Range("C1") = Application.WorksheetFunction.CountIf(.Range("A2:A" & x), "Apples") + Application.WorksheetFunction.CountIf(.Range("A2:A" & x), "Pears") and it worked, but i could figure out the other code. @Joe4 I tried the Or, but forgot the range part.

    thanks !!

    if i have a sheet named "apples", how would i reference that in the code? so basically count if the sheet name appears in A:A?

  5. #5
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,184
    Post Thanks / Like
    Mentioned
    87 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Count Multiple Items in Same Column

    Do you want to count how many times the sheet name "apple" appears in column A and also the sheet name "pears" appears?
    Last edited by mumps; Aug 13th, 2019 at 01:03 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Count Multiple Items in Same Column

    if i have a sheet named "apples", how would i reference that in the code?
    If I understand what you are asking, instead of:
    Code:
    With Sheet1
    you could use:
    Code:
    With Sheets("apples")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Posts
    491
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Multiple Items in Same Column

    sorry, how would i do something like: Range("C1") = WorksheetFunction.CountIf(.Range("A:A"), SheetName)

  8. #8
    Board Regular
    Join Date
    Oct 2009
    Posts
    491
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Multiple Items in Same Column

    Ideally i'd like to have a For Each ws in Worksheets function. So the code would cycle through each sheetname and put the count in C1 of the corresponding sheet.

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Count Multiple Items in Same Column

    If you set a Worksheet variable named "ws" and are looping through it, you can reference each name like:
    Code:
    ws.Name
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    Oct 2009
    Posts
    491
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Multiple Items in Same Column

    So this code is working great. My question is how would I split out the ws? Meaning the these ws.Cells would be in ws1 and ws.Name/ws.Range("C1") would be in ws2?


    Code:
    
    Dm ws As Worksheet
    With Sheet1 
       Dim x As Long   
    x = .Range("A" & Rows.Count).End(xlUp).Row
    End With
    
    For each ws in Worksheets
      Dim cnt As Integer
      cnt = 0
      For i = 1 To x
        If ws.Cells(i, 1).Value = ws.Name and ws.Cells(i,2).Value = "Football" Then
          cnt = cnt + 1
        End If
      Next i
    
    ws.Range("C1").Value = cnt
    
    Next
    Last edited by helpexcel; Aug 13th, 2019 at 03:19 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •