Count Multiple Items in Same Column

[helpexcel]

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?

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

 

[Joe4]

Change your IF line to:

If .Cells(i, 1).Value = "Apple" [COLOR=#ff0000]Or .Cells(i, 1).Value = "Pear"[/COLOR] Then

 

[mumps]

This one line of code will do it for "Apple":

Sub test()
    Range("C1") = WorksheetFunction.CountIf(Range("A:A"), "Apple")
End Sub

 

[helpexcel]

@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?

 

[mumps]

Do you want to count how many times the sheet name "apple" appears in column A and also the sheet name "pears" appears?

 

[Joe4]

if i have a sheet named "apples", how would i reference that in the code?

If I understand what you are asking, instead of:

With Sheet1

you could use:

With Sheets("apples")

 

[helpexcel]

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

 

[helpexcel]

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.

 

[Joe4]

If you set a Worksheet variable named "ws" and are looping through it, you can reference each name like:

ws.Name

 

[helpexcel]

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?

[COLOR=#333333]
Dm ws As Worksheet
[/COLOR][COLOR=#333333]With Sheet1 [/COLOR][COLOR=#333333]
   Dim x As Long[/COLOR]   
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