-1

I am using VBA in Excel 2011 (but saving to Excel 97 - 2004) to count all matching values in column C where the value is training. To do this I am using the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then

    Dim r As Range
    Dim c
    Set r = Sheets("Sheet2").Range("C:C")
    c = Application.WorksheetFunction.CountIf(r, "training")
    MsgBox "column C has " & c & " instances of 'training'"

    End If
End Sub

What I would like to do however is create a CountIfs statement that allows me to check if value "training" exists in column C and if the value 10 exists in another Column B and if both match within the same row then count all the rows where both values exist, otherwise do not count.

Would someone please be able to help me solve this?

pnuts
  • 58,317
  • 11
  • 87
  • 139
jack harper
  • 1
  • 1
  • im currently using excel for mac 2011 but im saving the workbook in excel 97 to 2004 as need it to be compatible with excel 2010 in windows – jack harper Oct 28 '14 at 21:10

2 Answers2

0

Similar to Count if two criteria match - EXCEL formula , you need a COUNTIFS function

=COUNTIFS(B2:B7, 10, C2:C7, "training")

edit

hadn't seen the update about the Excel version; COUNTIFS applies to 2007 +

does SUMPRODUCT work pree 2004?

=SUMPRODUCT((B2:B7=10)*(C2:C7="training"))

And in VBA i just tested this and it worked for me:

    myTest = Evaluate("=SUMPRODUCT((B2:B7=10)*(C2:C7=""training""))")
Community
  • 1
  • 1
tospig
  • 7,762
  • 14
  • 40
  • 79
0

I managed to solve it. Please see the below example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then

Dim r As Range
Dim p As Range
Dim t As Range
Dim u As Range
Dim c
Set r = Sheets("Sheet2").Range("C:C")
Set p = Sheets("Sheet2").Range("B:B")
Set t = Sheets("Sheet1").Range("B1")
Set u = Sheets("Sheet1").Range("C1")
c = WorksheetFunction.CountIfs(r, t, p, u)
MsgBox "column C has " & c & " instances of 'training'"

End If
End Sub
jack harper
  • 1
  • 1