Counting time values in two columns

[Eddie G.]

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

[Tom Schreiner]

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

[Dave Hawley]

Hi Eddie


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

[Aladin Akyurek]

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

[Yogi Anand]

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!

[Ian Mac]

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.