Counta with CountIf

[jpapanestor]

I have looked at other posting and haven't seem to have found a
similar one or haven't understood explanations.

Here goes.

Column A could have either a value of John or Wanda without 200 rows

Over in Column J there will be an "x" (on the same row of the name) if
column A has a value of John or Wanda.

Visual Aid:

Col A Col J
John x
Wanda x
John x
John x
Wanda x

Totals would be John 3 Wanda 2


Objective is to get a total of John's "x" 's AND a total of Wanda's
"x" 's.

Totals will then be captured on another worksheet using a formuala
pointing to their respective totals.

Sounds so easy, but not yet there.

Please help!
Thanks in advance.
Jay

 

[Incidental]

Hi Jay

I'm not sure how to do this with Countif but the code below will do it
for.

Option Explicit
Dim MyCell, MyRng As Range
Dim CntJohn, CntWanda As Integer
Private Sub CommandButton1_Click()

CntJohn = 0
CntWanda = 0

Set MyRng = [A1:A200]

For Each MyCell In MyRng

If MyCell.Value = "John" And _
MyCell.Offset(0, 9).Value = "x" Then

CntJohn = CntJohn + 1

ElseIf MyCell.Value = "Wanda" And _
MyCell.Offset(0, 9).Value = "x" Then

CntWanda = CntWanda + 1

End If

Next MyCell

MsgBox "There were " & CntJohn & " John's" & vbNewLine & _
"in the list with an 'x' in the Column J" & vbNewLine & _
"And there were " & CntWanda & " Wanda's" & vbNewLine & _
"in the list with an 'x' in the Column J"

End Sub

I hope this helps

Steve

 

[JP]

Why not just count the number of occurrences of John and Wanda
directly?

John's total:

=COUNTIF(A1:A200,"John")

Wanda's total:

=COUNTIF(A1:A200,"Wanda")

Sum of both:

=COUNTIF(A1:A200,"John")+COUNTIF(A1:A200,"Wanda")



HTH,
JP

 

[Bob Phillips]

=SUM(COUNTIF(A1:A200,{"John","Wanda"}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)