Thanks:  0
Likes:  0

# Thread: Counting time values in two columns

1. COL A COL B
1 17:30
2 17:45
3 17:00 17:30
4 17:44 17:23
5 17:11 17:34
6 17:13

Given the above cells of time data, i need a formula that will search a1:b20 and give me a count of time values, in this case the answer is 6, keeping in mind that a1 and a2 may or may not be blank. In other words, the first value in either col. A or B should be the first value counted, and the last value in either col. A or B should be counted, and the values in-between. I hope this makes sense....please help. thanks

Eddie G

2. Hi
Would the answer actually be 9?
If not then I do not understand your request.
Tom

3. Hi Eddie

I would suggest a simple Pivot Table or the use of the database functions in Excel.

4. On 2002-04-03 19:18, Eddie G. wrote:
COL A COL B
1 17:30
2 17:45
3 17:00 17:30
4 17:44 17:23
5 17:11 17:34
6 17:13

Given the above cells of time data, i need a formula that will search a1:b20 and give me a count of time values, in this case the answer is 6, keeping in mind that a1 and a2 may or may not be blank. In other words, the first value in either col. A or B should be the first value counted, and the last value in either col. A or B should be counted, and the values in-between. I hope this makes sense....please help. thanks

Eddie G
Is it:

=MAX(COUNT(A1:A20),COUNT(B1:B20))

5. On 2002-04-03 19:18, Eddie G. wrote:
COL A COL B
1 17:30
2 17:45
3 17:00 17:30
4 17:44 17:23
5 17:11 17:34
6 17:13

Given the above cells of time data, i need a formula that will search a1:b20 and give me a count of time values, in this case the answer is 6, keeping in mind that a1 and a2 may or may not be blank. In other words, the first value in either col. A or B should be the first value counted, and the last value in either col. A or B should be counted, and the values in-between. I hope this makes sense....please help. thanks

Eddie G
Hi Eddie:
If I understand you right ... in ColA and ColB, there would be either Time values, or the cells would be blank.
If this is the case, how about

=COUNT(A1:B20)

Please post back if it works for you ... otherwise explain a little further and let us take it fom there!

6. Eddie,

If I've read this correctly you want the count of any times in either A or B BUT NOT both, so:

A no time and B no time = 0
A time and B no time = 1
A no time and B time = 1
A time and B time ALSO = 1

if this is the case then Aladin's formula is the one to go with.

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