Two functions in one cell

[Thanh]

I am trying to incorporate two functions into one cell. I have the day in one column and time in another column

Day Time
14 8:01
14 8:05
14 9:06
15 8:50
15 8:02
16 2:00
16 2:50

e.g I want to find the number of times day 14 occurs between 8:00 and 9:00. The answer I would be looking for is 2 in this case. I have tried putting in the countif function along with "and" to separate the second function, but it doesn't work. Any suggestions?

[Al Chara]

Try the followin:

=SUMPRODUCT((A2:A8=14)*(B2:B8>=1/3)*(B2:B8<=3/8))

[Mark W.]

{=SUM((A2:A8=14)*(B2:B8>="8:00"+0)*(B2:B8<="9:00"+0))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[Aladin Akyurek]

On 2002-04-10 10:00, Thanh wrote:
I am trying to incorporate two functions into one cell. I have the day in one column and time in another column

Day Time
14 8:01
14 8:05
14 9:06
15 8:50
15 8:02
16 2:00
16 2:50

e.g I want to find the number of times day 14 occurs between 8:00 and 9:00. The answer I would be looking for is 2 in this case. I have tried putting in the countif function along with "and" to separate the second function, but it doesn't work. Any suggestions?

=SUMPRODUCT((B2:B8>=C1)*(B2:A8<=C2)*(A2:A3=C3))

where C1 houses the earlier time, C2 the later time, and C3 the day number.

You can also try DCOUNT:

In D1 enter: Time
In E1 enter: Time
In F1 enter: Day

In D2 enter: >=8:00
In E2 enter: <=9:00
In F2 enter: 14

=DCOUNT(A1:B8,1,D1:F2)

Aladin