Counting time values in two columns

Eddie G.

Board Regular
Joined
Feb 27, 2002
Messages
98
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi
Would the answer actually be 9?
If not then I do not understand your request.
Tom
 
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top