Countif in multiple worksheets

[Rob]

I have a workbook, with multiple (70) sheets , each with it's own name. Each
sheet states in cell D2 "Yes", "No", or "Not sure". In a summary worksheet I
would like to count the number of times "yes" has been stated in cell D2 of
all worksheets. With COUNTF I can't get the range right. Is it possible to do
this? I sure could use some help, thanx

Rob

 

[Bernard Liengme]

You cannot use COUNTIF (or SUMIF) with a 3D arguments such as
=COUNTIF(Sheet1:Sheet3!A20,"yes")

So you need a user-defined function
If the summary sheet is the first one on the tab list, then it is
Worksheet(1), the next is Worksheet(2) and so on.

Function countyes()
Application.Volatile
For j = 2 To 70
If Worksheets(j).Range("D2") = "Yes" Then
countyes = countyes + 1
End If
Next j
End Function

Not too familiar with VBA? See David McRitchie's site on "getting started"
with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes

 

[Rob]

Thanks, I used the user-defined function and it worked great. However it led
to another question: Is it possible to use extra criteria? For example:
If Cell D2 has "yes" or "No" and Cell E2 has "10" or "20" in each sheet, is
it possible to count the combinations of "yes" in D2 and "10" in E2 over all
sheets in the summary sheet?

Rob

 

[Bernard Liengme]

Not tested, but this should work

Function countyes()
Application.Volatile
For j = 2 To 70
With Worksheet(j)
If .Range("D2") = "Yes" Then
If .Range("E2") = 10 or .Range("E2") = 20 Then
countyes = countyes + 1
End if
End If
Next j
End Function

If this fails, email be privately (remove>TRUENORTH.) and I will sort it out
when I have more time
best wishes

 

[Rob]

I tried the code, however it gives a compile error at "With Worksheet(j). It
states that the sub of function is not defined. Appreciate the help.

Rob

 

[Bernard Liengme]

Function countyes()
Application.Volatile
mylast = Worksheets.Count
For j = 2 To mylast
With Worksheets(j)
If UCase(.Range("D2")) = "YES" Then
If .Range("E2") = 10 Or .Range("E2") = 20 Then
countyes = countyes + 1
End If
End If
End With
Next j
End Function


Sorry, I had omitted the last 's' from worksheets(j); and forgotten "End
With"
I have added the UCASE so the user may type: yes, Yes, or YES
Change 'mylast' in< For j = 2 To mylast > to some number if you want to
restrict which sheets are looked at

best wishes

 

[Rob]

It worked! Thank you, this saves a lot of work.

Rob