Countif in multiple worksheets

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

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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

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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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

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 (removeTRUENORTH.) and I will sort it out
when I have more time
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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

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" wrote:

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 (removeTRUENORTH.) and I will sort it out
when I have more time
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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" wrote:

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 (removeTRUENORTH.) and I will sort it
out
when I have more time
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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

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

Rob

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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" wrote:

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 (removeTRUENORTH.) and I will sort it
out
when I have more time
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
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