help with Count function

[Al]

I have a file that has 20 sheets. every sheet has a populated cells. I would
like to write a formula that counts the occurance of a cetrtain value on all
the sheets without having to write it on every sheet. the following works per
sheet only:

=COUNTIF(Sheet2!E19:G22,"FA")

However, I want to count from Sheet2 to Sheet20 I tried the following but
did not work:
=COUNTIF(Sheet2:Sheet4!E19:G22,"FA")
I got error: #VALUE!

One other IMPORTANT note. Unfortunatley, sheet tabs are not named sheet1
through sheet 20, they have their own different names
Any idea?
thanks
Al

 

[Don Guillett]

http://groups.google.com/group/micr...4/22d60bfc795a7766?lnk=st&q=#22d60bfc795a7766

 

[Gary''s Student]

Try this small UDF:

Function count_2_to_20(s As String, v As String) As Integer
count_2_to_20 = 0
For i = 2 To 20
Set ws = Sheets(i)
Set r = ws.Range(s)
For Each rr In r
If rr.Value = v Then
count_2_to_20 = count_2_to_20 + 1
End If
Next
Next
End Function


In a worksheet cell, enter:

=count_2_to_20("E19:G20","FA")

WARNING: As coded, this function is not volatile. It will not refresh
automatically. You must refresh the function manually if the data on the
sheets changes

 

[Bob Phillips]

Put the sheet names in M1:M19

=SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M19&"'!E19:G22"),"FA"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)