Count Multiple Items in Same Column

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
656
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Change your IF line to:
Code:
If .Cells(i, 1).Value = "Apple" [COLOR=#ff0000]Or .Cells(i, 1).Value = "Pear"[/COLOR] Then
 
Last edited:
Upvote 0
This one line of code will do it for "Apple":
Code:
Sub test()
    Range("C1") = WorksheetFunction.CountIf(Range("A:A"), "Apple")
End Sub
 
Upvote 0
@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?
 
Upvote 0
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:
Upvote 0
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")
 
Upvote 0
sorry, how would i do something like: Range("C1") = WorksheetFunction.CountIf(.Range("A:A"), SheetName)
 
Upvote 0
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.
 
Upvote 0
If you set a Worksheet variable named "ws" and are looping through it, you can reference each name like:
Code:
ws.Name
 
Upvote 0
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:
[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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top