I am struggling to get a column of talk times from our call center to total
up. The format is 0:00:05, for example, and represents the output from the
phone equipment. The report shows the agent name, and the total talk time.
I'm interested in getting a total off all the time all the agents spent on
the phone, and I'm stumped. It just won't =sum(b2:b248). It will do
=b2+b3+b4, but that is going to be difficult when the report is several
thousand entries long. I can use a different column, and get running totals,
but I am puzzled why I can't just get a total from the column. Does anyone
have any insight into the workings of Excel 2007?
--
Thanks, Randy

It's possible that you are trying to sum things that aren't numbers. In an
adjacent column, put something like this to test

=ISNUMBER(A1)
--
HTH,
Barb Reinhardt



"Randy Rich" wrote:

> I am struggling to get a column of talk times from our call center to total
> up. The format is 0:00:05, for example, and represents the output from the
> phone equipment. The report shows the agent name, and the total talk time.
> I'm interested in getting a total off all the time all the agents spent on
> the phone, and I'm stumped. It just won't =sum(b2:b248). It will do
> =b2+b3+b4, but that is going to be difficult when the report is several
> thousand entries long. I can use a different column, and get running totals,
> but I am puzzled why I can't just get a total from the column. Does anyone
> have any insight into the workings of Excel 2007?
> --
> Thanks, Randy

What result are you getting? Perhaps some of the values in column B are the
result of a formula. If there are any errors in the column, then your SUM
formula may also produce an error. There may be other ways to test column B
for errors, but here's one way.

=SUMPRODUCT(--(ISERROR(B2:B248)))

If the result is greater than zero, then there is that many cells in your
range that result in an error.

Does that help?
Paul

--

"Randy Rich" <(E-Mail Removed)> wrote in message
news:CA55FA0F-9548-4B8F-B9BE-(E-Mail Removed)...
>I am struggling to get a column of talk times from our call center to total
> up. The format is 0:00:05, for example, and represents the output from
> the
> phone equipment. The report shows the agent name, and the total talk
> time.
> I'm interested in getting a total off all the time all the agents spent on
> the phone, and I'm stumped. It just won't =sum(b2:b248). It will do
> =b2+b3+b4, but that is going to be difficult when the report is several
> thousand entries long. I can use a different column, and get running
> totals,
> but I am puzzled why I can't just get a total from the column. Does
> anyone
> have any insight into the workings of Excel 2007?
> --
> Thanks, Randy

I get a column of TRUE every time I test.
--
Thanks, Randy


"Barb Reinhardt" wrote:

> It's possible that you are trying to sum things that aren't numbers. In an
> adjacent column, put something like this to test
>
> =ISNUMBER(A1)
> --
> HTH,
> Barb Reinhardt
>
>
>
> "Randy Rich" wrote:
>
> > I am struggling to get a column of talk times from our call center to total
> > up. The format is 0:00:05, for example, and represents the output from the
> > phone equipment. The report shows the agent name, and the total talk time.
> > I'm interested in getting a total off all the time all the agents spent on
> > the phone, and I'm stumped. It just won't =sum(b2:b248). It will do
> > =b2+b3+b4, but that is going to be difficult when the report is several
> > thousand entries long. I can use a different column, and get running totals,
> > but I am puzzled why I can't just get a total from the column. Does anyone
> > have any insight into the workings of Excel 2007?
> > --
> > Thanks, Randy

Thanks for that test PCLIVE, but my result was 0. I've got to believe it is
something to do with the data format, but I honestly can't tell what it is.
I've tried a bunch of different cell formats, hoping each one will give me
the total I need. I end up with something that says 12:00:00 AM or something
similar. I even ran a pivot table, trying to get it to sum that way. One
agent got a 5 second total. Everyone else was zero. I tried to find out
which entry at least got me a 5, and from what I can tell it was the one I
entered by hand. However, it seems to work flawlessly when I do the
additional column and get the 'running total' - so selecting individual cells
seems to work. Highlighting the column gives me the 'count' at the bottom of
the page, but not the sum or the average. It's frustrating.
--
Thanks, Randy


"PCLIVE" wrote:

> What result are you getting? Perhaps some of the values in column B are the
> result of a formula. If there are any errors in the column, then your SUM
> formula may also produce an error. There may be other ways to test column B
> for errors, but here's one way.
>
> =SUMPRODUCT(--(ISERROR(B2:B248)))
>
> If the result is greater than zero, then there is that many cells in your
> range that result in an error.
>
> Does that help?
> Paul
>
> --
>
> "Randy Rich" <(E-Mail Removed)> wrote in message
> news:CA55FA0F-9548-4B8F-B9BE-(E-Mail Removed)...
> >I am struggling to get a column of talk times from our call center to total
> > up. The format is 0:00:05, for example, and represents the output from
> > the
> > phone equipment. The report shows the agent name, and the total talk
> > time.
> > I'm interested in getting a total off all the time all the agents spent on
> > the phone, and I'm stumped. It just won't =sum(b2:b248). It will do
> > =b2+b3+b4, but that is going to be difficult when the report is several
> > thousand entries long. I can use a different column, and get running
> > totals,
> > but I am puzzled why I can't just get a total from the column. Does
> > anyone
> > have any insight into the workings of Excel 2007?
> > --
> > Thanks, Randy
>
>
>

Randy,

In order to find the cell, or cells, causing the problem, try
highlighting the column, starting at the top, until there is no sum in
the status bar. Investigate the difference between the cells giving a
sum and those which do not.

HTH

tim

Randy Rich wrote:
> Thanks for that test PCLIVE, but my result was 0. I've got to believe it is
> something to do with the data format, but I honestly can't tell what it is.
> I've tried a bunch of different cell formats, hoping each one will give me
> the total I need. I end up with something that says 12:00:00 AM or something
> similar. I even ran a pivot table, trying to get it to sum that way. One
> agent got a 5 second total. Everyone else was zero. I tried to find out
> which entry at least got me a 5, and from what I can tell it was the one I
> entered by hand. However, it seems to work flawlessly when I do the
> additional column and get the 'running total' - so selecting individual cells
> seems to work. Highlighting the column gives me the 'count' at the bottom of
> the page, but not the sum or the average. It's frustrating.

Thanks Tim, As it turns out none of them give me a sum and the bottom. My
'count' increases, but at no time does a sum ever appear. I've been reading
about others who have a similar frustration, and one user just copied the
column of numbers into notepad and pasted them back in. I'm going to try
that next.
--
Thanks, Randy


"Tim Otero" wrote:

> Randy,
>
> In order to find the cell, or cells, causing the problem, try
> highlighting the column, starting at the top, until there is no sum in
> the status bar. Investigate the difference between the cells giving a
> sum and those which do not.
>
> HTH
>
> tim
>
> Randy Rich wrote:
> > Thanks for that test PCLIVE, but my result was 0. I've got to believe it is
> > something to do with the data format, but I honestly can't tell what it is.
> > I've tried a bunch of different cell formats, hoping each one will give me
> > the total I need. I end up with something that says 12:00:00 AM or something
> > similar. I even ran a pivot table, trying to get it to sum that way. One
> > agent got a 5 second total. Everyone else was zero. I tried to find out
> > which entry at least got me a 5, and from what I can tell it was the one I
> > entered by hand. However, it seems to work flawlessly when I do the
> > additional column and get the 'running total' - so selecting individual cells
> > seems to work. Highlighting the column gives me the 'count' at the bottom of
> > the page, but not the sum or the average. It's frustrating.
>

Hi everyone, I'm so grateful for your insights and ideas. As I've been
reading other posts in this forum, I found one user who copied out the column
of data into notepad and then pasted it back in. I thought I'd try that too,
and for some reason that works just fine. That gets me out of my puzzle
temporarily, but I'm sure that I'm going to get similar reports out of this
equipment - and this approach seems an onerous way of getting my results.
I'd sure be interested if someone can identify what the real challenge is so
this step can be avoided in the future. Any MVPs have the needed insight?
--
Thanks, Randy


"Randy Rich" wrote:

> I am struggling to get a column of talk times from our call center to total
> up. The format is 0:00:05, for example, and represents the output from the
> phone equipment. The report shows the agent name, and the total talk time.
> I'm interested in getting a total off all the time all the agents spent on
> the phone, and I'm stumped. It just won't =sum(b2:b248). It will do
> =b2+b3+b4, but that is going to be difficult when the report is several
> thousand entries long. I can use a different column, and get running totals,
> but I am puzzled why I can't just get a total from the column. Does anyone
> have any insight into the workings of Excel 2007?
> --
> Thanks, Randy

If you'd like, send me a sample and I'll get back to you.

Cliff Edwards

I'm beginning to wonder if you are getting sums of times (in time format) and
haven't converted them to hours by multiplying the sum by 24.
--
HTH,
Barb Reinhardt



"Randy Rich" wrote:

> I get a column of TRUE every time I test.
> --
> Thanks, Randy
>
>
> "Barb Reinhardt" wrote:
>
> > It's possible that you are trying to sum things that aren't numbers. In an
> > adjacent column, put something like this to test
> >
> > =ISNUMBER(A1)
> > --
> > HTH,
> > Barb Reinhardt
> >
> >
> >
> > "Randy Rich" wrote:
> >
> > > I am struggling to get a column of talk times from our call center to total
> > > up. The format is 0:00:05, for example, and represents the output from the
> > > phone equipment. The report shows the agent name, and the total talk time.
> > > I'm interested in getting a total off all the time all the agents spent on
> > > the phone, and I'm stumped. It just won't =sum(b2:b248). It will do
> > > =b2+b3+b4, but that is going to be difficult when the report is several
> > > thousand entries long. I can use a different column, and get running totals,
> > > but I am puzzled why I can't just get a total from the column. Does anyone
> > > have any insight into the workings of Excel 2007?
> > > --
> > > Thanks, Randy