Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: Comparing multiple values and counting if criteria meets

    Hi,

    I have a table of times (in text format) with two columns.

    Example:

    Col A Col B
    0:15 03:30
    09:00 14:00
    15:00 22:39
    23:15 03:45

    The first time-pair (0.15-03.30) is on the same day. The last (23.15 - 03.45) is spread to two different days.

    I need to count the number of rows where Col B value is between for example 03-04 BUT only if they are in the same day (if col A < Col B).
    So in the example table I would get results of 1 (even tho there are 2 instanses where col B has the value between 03-04)
    And naturally I need another formula to count the instances where the COL B value is between 03-04 BUT only if they time spreads to two different days (Col A > Col B).

    Any Ideas... been trying countif, count with If, sumproduct.. but haven't come up with a working formula. I'd like to avoid macros, but if it is the only way - then those are ok too.

    - FinExcel -

  2. #2
    Join Date
    May 2009
    Posts
    258
    Try this for A < B:
    Code:
    =SUMPRODUCT(--(A1:A4<B1:B4),--(B1:B4>=TIME(3,0,0)),--(B1:B4<=TIME(4,0,0)))
    This for A > B:
    Code:
    =SUMPRODUCT(--(A1:A4>B1:B4),--(B1:B4>=TIME(3,0,0)),--(B1:B4<=TIME(4,0,0)))
    If you want to put the high and low time values in cells, you can change them easier than using TIME(3,0,0) and TIME(4,0,0).

    Regards,

    Ax

  3. #3
    Join Date
    May 2009
    Posts
    2
    Hey,

    For some reason the formula didn't work at first (must've misspelled somewhere), but after toying around with it I got it working perfectly.

    Thank you very much - now that I got it working I can go have a nice weekend without having to worry about this... cheers to that

    - FinExcel -

  4. #4
    Join Date
    May 2009
    Posts
    258
    Excellent! Glad to hear it.

Posting Permissions

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