Count occurance of a string across numerous worksheets

[Cameron]

Hi all,

Have a "Control Page" and numerous other sheets.
I've now listed all workseets on the "Control Page" eg:

..
A
B

10 Feb04-Mar04
11 Jan04-Feb04
12 Dec03-Jan04
13 Nov03-Dec03
14 Oct03-Nov03
15 Sep03-Oct03
16 Aug03-Sep03
17 Jul03-Aug03
18 Jun03-Jul03
19 May03-Jun03
20 Apr03-May03
21 Mar03-Apr03
22 Feb03-Mar03
23 Jan03-Feb03
24 Dec02-Jan03
25 Nov02-Dec02


Im trying to count the number of times a specific string "Internet Usage -
Prepaid IP Hours " (34chars) appears in the range(D3550) of each worksheet
using the reference in Column A and place the result in Column B.

Can I do this??

Any help apreciated.

Cam

 

[Dave Peterson]

=COUNTIF(INDIRECT("'"&A10&"'!d3:d550"),"Internet Usage - Prepaid IP Hours ")

dragged down.

 

[Cameron]

Thanks Dave.
I amended it to look like this ... (dragged down)
=IF(B10<>"",COUNTIF(INDIRECT("'"&B10&"'!D3550"),"Internet Usage -
Prepaid IP Hours*")," ")

My next few issues is to get the value from a cell, eg "84:12 Hours".
The `Hours` cell is located in Column D at the bottom of the table which
varies in size from month to month.
The other is stripping the last 4 characters from each of the "Internet
Usage - Prepaid IP Hours*" statements and adding a count to the reflective
tally below based on how much time was spend online. (Hope that makes some
kinda sence)
< 2 Mins > 2 Mins

25 Mins < 30 Mins >30 Mins < 45 Mins > 45 Mins < 1 Hour > 1 Hour

< 2 Hours > 2 Hours < 3 Hours >3 Hours < 4 Hours

10 20 30 45 55 60 75 110 ?? ?? ?? ??


All of which should tally to the initial COUNTIF for the reflective period.

Also, I was unable to find anything in help for the INDIRECT statement used
(trying to understand how it works), can you throw any links my way??

Cheers,
Cam

 

[Dave Peterson]

=Indirect() is essentially a function that converts text to a reference.

For example, if you had $A$1 in a cell (say B1) but it could vary based on input
from the user or the results of a formula, but you needed a the value that was
in the cell whose address was stored in B1, you could use =indirect(b1).

It's kind of a pointer function that says point at what this string contains.

Try a few small experiments.

Put some stuff in A1:A10
Put the characters A1 in B1
Put =indirect(b1) in C1

Now change the value in B1 to A3, A5, A9.

If you want to strip the last 4 characters from a string, you can use:
=right(a1,4)
(but this returns text)

If you wanted to convert it to numbers,
=--right(a1,4)

You may want to look at =right(), =left(), =mid(), along with =search() in
excel's help.