Thanks:  0
Likes:  0

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

1. ```

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

3. 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

4. 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. 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. 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

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

Clear and concise.
Tom

8. 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. 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. 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.

## User Tag List

#### Posting Permissions

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