excel - Count rows where value exists in Column A and other value exists in Column B -

excel - Count rows where value exists in Column A and other value exists in Column B -

i using vba in excel 2011 (but saving excel 97 - 2004) count matching values in column c value training. using next code:

private sub worksheet_selectionchange(byval target range) if target.address = "$a$1" dim r 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 create countifs statement allows me check if value "training" exists in column c , if value 10 exists in column b , if both match within same row count rows both values exist, otherwise not count.

would please able help me solve this?

similar count if 2 criteria match - excel formula , need countifs function

=countifs(b2:b7, 10, c2:c7, "training")

edit

hadn't seen update excel version; countifs applies 2007 +

does sumproduct work pree 2004?

=sumproduct((b2:b7=10)*(c2:c7="training"))

and in vba tested , worked me:

mytest = evaluate("=sumproduct((b2:b7=10)*(c2:c7=""training""))")

excel vba count excel-2011