Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Seeking a Formula or Two... Thanks!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    Column D | Column E
    |
    UP-Time 14:00:13
    DOWN-Time 0:01:06
    UP-Time 0:00:30
    DOWN-Time 4:52:28




    Hi
    I am looking for a formula which
    will run down Column D and total
    all times in Column E (HH:MM:SS)
    for "UP-Time".

    I guess I can apply the same
    formula to "DOWN-Time"

    Since I'm here...

    I will also be attempting
    to get the % of UP-Time
    and the % of DOWN-Time
    from these totals...

    Thanks,
    Tom

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-04 15:45, TsTom wrote:


    Column D | Column E
    |
    UP-Time 14:00:13
    DOWN-Time 0:01:06
    UP-Time 0:00:30
    DOWN-Time 4:52:28




    Hi
    I am looking for a formula which
    will run down Column D and total
    all times in Column E (HH:MM:SS)
    for "UP-Time".

    I guess I can apply the same
    formula to "DOWN-Time"

    Since I'm here...

    I will also be attempting
    to get the % of UP-Time
    and the % of DOWN-Time
    from these totals...

    Thanks,
    Tom
    =SUMIF(D2:D100,"UP-Time",E2:E100)

    Same for the DOWN-Time: Just replace the condition (2nd) argument.

    Custom format the formula cells as

    [hh]:mm:ss

    Aladin

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-04 15:45, TsTom wrote:


    Column D | Column E
    |
    UP-Time 14:00:13
    DOWN-Time 0:01:06
    UP-Time 0:00:30
    DOWN-Time 4:52:28




    Hi
    I am looking for a formula which
    will run down Column D and total
    all times in Column E (HH:MM:SS)
    for "UP-Time".

    I guess I can apply the same
    formula to "DOWN-Time"

    Since I'm here...

    I will also be attempting
    to get the % of UP-Time
    and the % of DOWN-Time
    from these totals...

    Thanks,
    Tom
    Hi Tom...This worked for me;

    =SUMPRODUCT((E4:E10)*(D4:D10="UP-Time"))

    Note cell formated as; [hh]:mm:ss to show
    total over 24hrs


    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Woops!! Aladin beat me by 1 Min



    _________________

    Kind Regards,
    Ivan F Moala
    Have a Nice day

    [ This Message was edited by: Ivan F Moala on 2002-05-04 16:04 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You guys are great!
    I used both of them.

    I had no clue you could do that with the Range on the end with SumIf.
    I always thought that this was all you could do:
    =SumIf(Range,Condition)

    I have never used the SumProduct before.
    Will need to check it out more thouroughly.

    I appreciate it. Am forcing myself to learn these functions instead of resorting to VBA too much of the time.

    Have a good one!
    Tom

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-04 16:07, TsTom wrote:
    You guys are great!
    I used both of them.

    I had no clue you could do that with the Range on the end with SumIf.
    I always thought that this was all you could do:
    =SumIf(Range,Condition)

    I have never used the SumProduct before.
    Will need to check it out more thouroughly.

    I appreciate it. Am forcing myself to learn these functions instead of resorting to VBA too much of the time.

    Have a good one!
    Tom
    In situations of single condition summing is SUMIF is more appropriate & it's more efficient than SUMPRODUCT which is, although expensive, more effective in situations of multiconditional summing (it has a counterpart D-function called DSUM).

    The full syntax for SUMIF is:

    SUMIF(Range1,Condition,Range2)

    where Condition is expected to hold for Range1. Moreover, Range2 can be the same as Range1:

    SUMIF(Range,Condition,Range)

    which gets shortened to:

    SUMIF(Range,Condition).

    On SUMPRODUCT, see:

    http://www.mrexcel.com/wwwboard/messages/8961.html

    Aladin



    [ This Message was edited by: Aladin Akyurek on 2002-05-04 16:34 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin.
    Clear and concise.
    Great to have this learning for free!
    Tom

  8. #8
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you don't want to have to include the text criteria use this formula in F:F
    =SUMIF(D2:D100,D2:D100,E2:E100)

    With this form of sumif colmn F will display both the downtime and uptime sums.

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    When you get to similar, but more complicated stuff, try the Conditional Sum Add-in. This will show you how to create array formulas. (Can do more than just sum.) Remeber to use CTRL-SHIFT-ENTER when entering the formulas.

  10. #10
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yea the array formulas are very useful and flexable ie. =SUM(IF(($A1:$A$10=A15)*($B1:$B10=B15)*($C1:$C10=C15),$D$1:$D$10))

    is a SumIf that only sums column D when conditions have been met in column A,B and C.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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