help with Count function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Al - 18 Mar 2008 18:31 GMT

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

Reply to this Message

Don Guillett - 18 Mar 2008 18:41 GMT

http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/
thread/f16c5f5a37c98b14/22d60bfc795a7766?lnk=st&q=#22d60bfc795a7766

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have a file that has 20 sheets. every sheet has a populated cells. I
>would
[quoted text clipped - 16 lines]
> thanks
> Al

Reply to this Message

Gary''s Student - 18 Mar 2008 18:48 GMT

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

Signature

Gary''s Student - gsnu200774

> 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
[quoted text clipped - 13 lines]
> thanks
> Al

Reply to this Message

Bob Phillips - 18 Mar 2008 18:54 GMT

Put the sheet names in M1:M19

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

Signature

---
HTH

Bob

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

>I have a file that has 20 sheets. every sheet has a populated cells. I
>would
[quoted text clipped - 16 lines]
> thanks
> Al

Reply to this Message