Seeking a Formula or Two... Thanks!

[Legacy 98055]

<pre>

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

</pre>


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

 

[Legacy 98055]

Thanks for all the replies everyone...

 

[Aladin Akyurek]

On 2002-05-04 18:13, Nimrod wrote:
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.

I suspect that you mean:

=SUMIF(D$2:D$100,D$2:D$100,E$2:E$100)

to be copied down from F2 on.

How does this differ from:

=SUMIF(D$2:D$100,D2,E$2:E$100)

to be copied down from F2 on.

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

is not meant to be entered in F2 and copied down. Rather, to be put somewhere outside the data area, along with another that computes a sum for "DOWN-Time". Or, better:

Enter in G2:G3 "UP-Time" and "DOWN-Time" respectively.

In H2 enter and copy down for all conds in G:

=SUMIF(D$2:D$100,G2,E$2:E$100)

Note. Yes, I know I left all this implicit (that is, replacing constants by cell refs) in my reply.

I don't see a good reason for: use this formula in F:F
=SUMIF(D2:D100,D2:D100,E2:E100) or for its correct form (see above), since that would waste resources in terms of space and time.

Regards,

Aladin

 

[Aladin Akyurek]

Brian,

Conditional Sum Wizard can also stay you in the way to understand the varied syntax that is required in devising formulas for conditional sums. Switch to the direct method as soon as possible, if not immediately .

On 2002-05-04 22:30, Nimrod wrote:
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.

Nimrod,

Did you know that you can rewrite

{=SUM(IF(($A1:$A$10=A15)*($B1:$B10=B15)*($C1:$C10=C15),$D$1:$D$10))}

as

{=SUM(($A1:$A$10=A15)*($B1:$B10=B15)*($C1:$C10=C15)*($D$1:$D$10))}

that is, composed of Boolean terms, which exactly equivalent to the array-grinding:

=SUMPRODUCT(($A1:$A$10=A15)*($B1:$B10=B15)*($C1:$C10=C15)*($D$1:$D$10))

or

=SUMPRODUCT(($A1:$A$10=A15)*($B1:$B10=B15)*($C1:$C10=C15),($D$1:$D$10))

Aladin