COUNTIF Problem I think

[Cameron]

Hi all,

Have numerous Worksheets.
On each worksheet is something similar to:
"Internet Usage - Prepaid IP Hours 1:10" or
"Internet Usage - Prepaid IP Hours 3:50" or
"Internet Usage - Prepaid IP Hours 0:10" in the range of D3550 of each
worksheet.
What I'm trying to do is get counts for the number of times an instance:
a) is less than 2 minutes
b) more than 2 minutes but less than 5 minutes
c) more than 5 minutes but less than 10 minutes
d) ...etc.

I had tried ...
=COUNTIF(INDIRECT("'"&B11&"'!D3550"),RIGHT(INDIRECT("'"&B11&"'!D3550"),L
EN(INDIRECT("'"&B11&"'!D3550"))-FIND("*",SUBSTITUTE(INDIRECT("'"&B11&"'!D3
550"),"
","*",LEN(INDIRECT("'"&B11&"'!D3550"))-LEN(SUBSTITUTE(INDIRECT("'"&B11&"'!
D3550")," ",""))))) > "1:00" )

and ...
=COUNTIF(INDIRECT("'"&B11&"'!D3550"),RIGHT(INDIRECT("'"&B11&"'!D3550"),4
) > "1:00" )

during experimentation, but results were not forth coming.

I have two issues:
a) Getting the time value from the string.
b) Counting the number of instances.

Any suggestions apreciated.

Cheers,
Cameron

 

[Bob Phillips]

Cameron,

This does both in one formula, for a time range of 2+ to 5 mins, but it can
only work on one sheet, so you need to add each sheet together

=SUMPRODUCT((IF(ISNUMBER(FIND("Prepaid IP
Hours",H1:H15)),TIMEVALUE(MID(H1:H15,FIND("Prepaid IP
Hours",H1:H15)+17,99)),0)>TIME(0,2,0))*(IF(ISNUMBER(FIND("Prepaid IP
Hours",H1:H15)),TIMEVALUE(MID(H1:H15,FIND("Prepaid IP
Hours",H1:H15)+17,99)),0)<=TIME(0,5,0)))

This is an arry formula, so commit with Ctrl-Shift-Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Cameron]

Bob,

Many thanks for the formula, I made one small amendment with the inclussion
of the INDIRECT command...

{=IF(B10<>"",SUMPRODUCT((IF(ISNUMBER(FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:
D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)>TIME(0,0,0))*(IF(ISNUMBER(F
IND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:
D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)<=TIME(0,2,0))),"")}

And is in use from F10:U33.

I have some scripting (very little) in this workbook and wondered if I
should move the above (or anything else for that matter) from the worksheet
level and code it instead?

The link (http://cam.wadla.com/Cut down copy of PowerUp Costings.xls) is a
cut-down-copy of my original spreadsheet which has now 17 worksheets.

Cheers,
Cam