Seeking a Formula or Two... Thanks!

L

Legacy 98055

Guest
<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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
On 2002-05-04 15:45, TsTom wrote:
<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

=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
 
Upvote 0
On 2002-05-04 15:45, TsTom wrote:
<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

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
 
Upvote 0
Woops!! Aladin beat me by 1 Min :biggrin:



_________________
01_pc.gif

Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><MARQUEE>Have a Nice day
sun.gif
</MARQUEE>
This message was edited by Ivan F Moala on 2002-05-04 16:04
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thanks Aladin.
Clear and concise.
Great to have this learning for free!
Tom
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top