Seeking a Formula or Two... Thanks!

[Tom Schreiner]

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

[Aladin Akyurek]

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

[Ivan F Moala]

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

[Ivan F Moala]

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 ]

[Tom Schreiner]

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

[Aladin Akyurek]

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 ]

[Tom Schreiner]

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

[Nimrod]

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.

[Brian]

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.

[Nimrod]

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.