=COUNTIF('Feb ''07'!G2:G4999, "<=:00:30")-COUNTIF('Feb ''07'!F2:F4999,
"System")

That's the beginning formula that I'm working with. Now, this is to
assist in tracking calls that come in that are less than or equal to
30 seconds, minus the calls that come in under the "System" heading.
Seems simple enough, however the oversight w/ the original formula is
that some of the calls that come in under the "System" heading now are
more than 30 seconds and it's throwing off the numbers. I've been
beating my head against my desk to try to figure out a formula and I'm
not succeeding in anything besides giving myself a headache.
Something tells me that I'm making this much harder than it should
be. Is there anyone that can help me out?

=SUMPRODUCT(--('Feb ''07'!G2:G4999<=--"00:30"),--('Feb
''07'!F2:F4999<"System"))


--
---
HTH

Bob

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



wrote in message
ups.com...
=COUNTIF('Feb ''07'!G2:G4999, "<=:00:30")-COUNTIF('Feb ''07'!F2:F4999,
"System")

That's the beginning formula that I'm working with. Now, this is to
assist in tracking calls that come in that are less than or equal to
30 seconds, minus the calls that come in under the "System" heading.
Seems simple enough, however the oversight w/ the original formula is
that some of the calls that come in under the "System" heading now are
more than 30 seconds and it's throwing off the numbers. I've been
beating my head against my desk to try to figure out a formula and I'm
not succeeding in anything besides giving myself a headache.
Something tells me that I'm making this much harder than it should
be. Is there anyone that can help me out?

"0:00:30"

"Bob Phillips" wrote:

=SUMPRODUCT(--('Feb ''07'!G2:G4999<=--"00:30"),--('Feb
''07'!F2:F4999<"System"))


--
---
HTH

Bob

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



wrote in message
ups.com...
=COUNTIF('Feb ''07'!G2:G4999, "<=:00:30")-COUNTIF('Feb ''07'!F2:F4999,
"System")

That's the beginning formula that I'm working with. Now, this is to
assist in tracking calls that come in that are less than or equal to
30 seconds, minus the calls that come in under the "System" heading.
Seems simple enough, however the oversight w/ the original formula is
that some of the calls that come in under the "System" heading now are
more than 30 seconds and it's throwing off the numbers. I've been
beating my head against my desk to try to figure out a formula and I'm
not succeeding in anything besides giving myself a headache.
Something tells me that I'm making this much harder than it should
be. Is there anyone that can help me out?

Well, I tried the formula you provided Bob, but it doesn't work
correctly. It gave me some number like 4897, and we don't even come
close to taking that many calls total, let alone just the ones that
are less than 30 seconds. Any other ideas? I've tried to set up an
array formula, but I just can't seem to get it.

Also on a side note, I don't mean to be rude and appreciate any help
that people can provide, but Teethless mama, I honestly don't know
what kind of HELP that reply was.

I think Bob's formula is counting anything less than 30 minutes, and
Teethless Mama was correcting this. Try this modification:

=SUMPRODUCT(--('Feb ''07'!G2:G4999<=--"00:00:30"),--('Feb ''07'!
F2:F4999<"System"))

Hope this helps.

Pete

On Feb 8, 3:35 pm, wrote:
Well, I tried the formula you provided Bob, but it doesn't work
correctly. It gave me some number like 4897, and we don't even come
close to taking that many calls total, let alone just the ones that
are less than 30 seconds. Any other ideas? I've tried to set up an
array formula, but I just can't seem to get it.

Also on a side note, I don't mean to be rude and appreciate any help
that people can provide, but Teethless mama, I honestly don't know
what kind of HELP that reply was.

Pete, thanks for the quick reply. I did try the updated formula that
you provided, however I got the exact same result as with Bob's
formula. Is there more information that I can try to provide in order
to assist figuring this out?

Layout a sample of the data and the expected result. Be sure to count some
of the items that count wrongly in your formula.

--
---
HTH

Bob

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



wrote in message
oups.com...
Pete, thanks for the quick reply. I did try the updated formula that
you provided, however I got the exact same result as with Bob's
formula. Is there more information that I can try to provide in order
to assist figuring this out?

Alright, I'll do my best to try to get this formatted so it hopefully
makes a little sense.

Column F Column G
HNR-B13 :01:09
System :00:10
HNR-B13 :10:40
System :00:03
System :00:08
HNR-B13 :03:13
System :00:21
System :00:36
HNR-C17 :04:08
HNR-B12 :07:14
HNR-B12 :02:13
HNR-B14 :00:06
HNR-B02 :00:08

There's the sample of the data. Now, the result needs to be all of
the calls that are less than or equal to 30 seconds, minus all of the
calls in calls that come in under the System heading in column F that
are less than or equal to 30 seconds. So in this example there are 6
total calls that are <= :00:30 and there are only 4 calls w/ the
System heading, therefore the desired result is 2. With the original
formula that I was using, it would have generated a result of 1.

=SUMPRODUCT(--(ISNUMBER('Feb ''07'!G2:G4999)),
--('Feb ''07'!G2:G4999<=--"00:30"),--('Feb ''07'!F2:F4999<"System"))

--
---
HTH

Bob

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



wrote in message
oups.com...
Alright, I'll do my best to try to get this formatted so it hopefully
makes a little sense.

Column F Column G
HNR-B13 :01:09
System :00:10
HNR-B13 :10:40
System :00:03
System :00:08
HNR-B13 :03:13
System :00:21
System :00:36
HNR-C17 :04:08
HNR-B12 :07:14
HNR-B12 :02:13
HNR-B14 :00:06
HNR-B02 :00:08

There's the sample of the data. Now, the result needs to be all of
the calls that are less than or equal to 30 seconds, minus all of the
calls in calls that come in under the System heading in column F that
are less than or equal to 30 seconds. So in this example there are 6
total calls that are <= :00:30 and there are only 4 calls w/ the
System heading, therefore the desired result is 2. With the original
formula that I was using, it would have generated a result of 1.

Thanks again for the reply Bob. However, that formula didn't work
either. It just gave me a result of 0. I even tried it using the
small sample of data that I had in my previous post and still got a
result of 0.

Not Bob but I took your sample data and added a zero to the times.

0:01:09
0.00:10
0:10:40

etc.

Works fine after that.

Here is a macro to add a zero to each cell.

Sub Add_Text_Left()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter the Text to Add")
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub


Gord Dibben MS Excel MVP

On 9 Feb 2007 07:10:04 -0800, wrote:

Thanks again for the reply Bob. However, that formula didn't work
either. It just gave me a result of 0. I even tried it using the
small sample of data that I had in my previous post and still got a
result of 0.

On Feb 9, 12:33 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Not Bob but I took your sample data and added a zero to the times.

0:01:09
0.00:10
0:10:40

etc.

Works fine after that.

Here is a macro to add a zero to each cell.

Sub Add_Text_Left()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter the Text to Add")
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub

Gord Dibben MS Excel MVP


Alright, wonderful. Yes adding a zero did in fact get it to work for
me. Now I just have to figure out the macro issue as I've never had
reason to use macros before. Truely appreciate all the assistance on
this issue.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro, after selecting your range, by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On 9 Feb 2007 10:55:09 -0800, wrote:

On Feb 9, 12:33 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Not Bob but I took your sample data and added a zero to the times.

0:01:09
0.00:10
0:10:40

etc.

Works fine after that.

Here is a macro to add a zero to each cell.

Sub Add_Text_Left()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter the Text to Add")
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub

Gord Dibben MS Excel MVP


Alright, wonderful. Yes adding a zero did in fact get it to work for
me. Now I just have to figure out the macro issue as I've never had
reason to use macros before. Truely appreciate all the assistance on
this issue.