+ Reply to Thread
Results 1 to 8 of 8

Format problem - Get sum of these times

  1. #1
    Registered User
    Join Date
    05-25-2010
    Location
    Trinidad
    MS-Off Ver
    Excel 2007
    Posts
    29

    Format problem - Get sum of these times

    Good Day Folks,

    I kindly request your assistance in completing this challenge i'm having. The attached file shows the average speed of answering of phone calls that i need to get a total sum for. Problem is due to the formatting, a standard sum on these cells would not giving an answer.

    Can anyone suggest the route i should take to accomplish this sum?

    Much thanks,
    Avinash
    Attached Files Attached Files
    Last edited by Avinash Beepath; 01-27-2011 at 02:47 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Format problem - Get sum of these times

    In E2 and copy down,

    =--IF(ISNUMBER(D2), D2/60, "0:0" & D2)

    That gives numbers you can average.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-25-2010
    Location
    Trinidad
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Format problem - Get sum of these times

    Thank you very much...your assistance is greatly appreciated!

  4. #4
    Registered User
    Join Date
    05-25-2010
    Location
    Trinidad
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Array Formula with SumIFS

    Mod. edit: below three posts were originally on: http://www.excelforum.com/excel-gene...th-sumifs.html - OP posted to wrong thread

    Hi DonkeyOte,

    Based on the formula you assisted me above, I'm trying to use it in VBA but danggg I'm struggling alot -I keep getting alot of errors with the quotes (i'm sure this is the reason). Here is my code below can you review and suggest what might be causing the error. I want to use the formula for the entire range D.

    Please Login or Register  to view this content.
    Thank you for your review.

    Avinash
    Last edited by DonkeyOte; 02-07-2011 at 02:05 PM.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Array Formula with SumIFS

    I don't follow the link between your earlier sample file / my suggestions and your code above... perhaps you could post a further sample file to better illustrate current requirements ?

  6. #6
    Registered User
    Join Date
    05-25-2010
    Location
    Trinidad
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Array Formula with SumIFS

    Sorry for the unclear description of what I'm referring too.

    See attach - In column E, from rows (7 to 31), i'm trying to replicate that great formula you gave me based on the figures in column D but i cant get it to work... hopefully i've made it a bit clearer for you now.

    Thank You,
    Avinash
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Format problem - Get sum of these times

    Confusion because you posted to the wrong thread - I've moved the posts

    Perhaps:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-25-2010
    Location
    Trinidad
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Format problem - Get sum of these times

    I wonder how that thread incident happened lol

    Anyways you guys are really excel gurus. There's no way i would have came up with that "With" statement using those ranges. This is very efficient. I'll try to deploy this method on other macros i've created.

    Thank you again shg and donkeyote for the assistance.

    Best Regards,
    Avinash

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1