Thanks:  0
Likes:  0

1. Aloha,

I would like to count these events as 1. Lost again

d1-c1>30
c1>a1
d1>b1

Mahalo

2. Hi
I assume you are looking for an If statement.

=IF(AND(D1-C1>30,C1>A1,C1>A1), "true", "false")

Replace "true" with what you want to see in the cell if its true.
Replace "false" with what you want to see in your cell if its false

Hope this is what you are looking for
Derek

3. Derek,

Not quite. I have time values in ABCD

10:00 10:40 10:10 10:41

It meets the criteria, but doesn't count as 1

Mahalo

4. question - if you have time values in the cells, how will D1-C1>30 ever be true? am i missing something? do you have dates as well?

5. d1-c1 is 31 mins

6. oh, i see. i thought it would count that as .517. have never been good with time formats...thanks

7. hang on. =D1-31 returns .31, not 31, so you will have amend the test condition from 30 to .30 (or will it be .5?)

8. I tried formatting target cell to number, general, and time..no go. changed 31 to .31 and :31 doesn't count it ..

9. On 2002-02-28 20:31, Brian from Maui wrote:
Aloha,

I would like to count these events as 1. Lost again

d1-c1>30
c1>a1
d1>b1

Mahalo
Brian,

Consider the following sample, where the funny numbers are Excel's internal representation of actual times,

{0.416666666666667,0.444444444444444,0.423611111111111,0.445138888888889;
0.40625,0.427083333333333,0.409722222222222,0.430555555555556;
0.427083333333333,0.447916666666667,0.430555555555556,0.456944444444444}

in A1:D3.

In E1 enter & copy down:

=((D1-C1)*1440>30)*(C1>A1)*(D1>B1)

You can then sum column E to get desired count:

=SUM(E:E)

or

=SUM(E1:E3)

However, you can also arrive at the desired count by means of a single formula:

In F1 enter:

=SUMPRODUCT(((D1:D3-C1:C3)*1440>30)*(C1:C3>A1:A3)*(D1:D3>B1:B3))

[ This Message was edited by: Aladin Akyurek on 2002-03-01 00:35 ]

"AWESOME"

Did you read my funny story?

## 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
•