Counting time values in two columns
Counting time values in two columns
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Counting time values in two columns

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Eddie


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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.

    "Have a good time......all the time"
    Ian Mac

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com