Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counta with CountIf
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counta with CountIf
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counta with CountIf
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 On Dec 20, 11:13 am, wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counta with CountIf
=SUM(COUNTIF(A1:A200,{"John","Wanda"}))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTA or COUNTIF?? | Excel Discussion (Misc queries) | |||
Need help with Count, Counta, Countif | Excel Discussion (Misc queries) | |||
If with countif or counta | Excel Worksheet Functions | |||
CountIF, CountA,Which one? or neither? | Excel Discussion (Misc queries) | |||
COUNTA, COUNTIF? | Excel Worksheet Functions |