+ Reply to Thread
Results 1 to 3 of 3

count cells if ADJACENT row values meet criteria

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    count cells if ADJACENT row values meet criteria

    I have a 2 column list of datetimes and values from a network log. I need to be able to count the occurrences in the list where 4 values occur next to each other vertically greater than 1000.

    I think I could do this in VBA by stepping through the array (for i in B:B) but I am looking for a way to have this as a formula instead.

    example:
    datetime(A) value(B)
    10/9/12 7:02 PM 65
    10/9/12 7:03 PM 71
    10/9/12 7:04 PM 66
    10/9/12 7:05 PM 72
    10/9/12 7:06 PM 69
    10/9/12 7:07 PM 2571
    10/9/12 7:08 PM 1580
    10/9/12 7:09 PM 78
    10/9/12 7:10 PM 1417
    10/9/12 7:11 PM 3560
    10/9/12 7:12 PM 73
    10/9/12 7:13 PM 87
    10/9/12 7:14 PM 68
    10/9/12 7:15 PM 2975
    10/9/12 7:17 PM 36803
    10/9/12 7:17 PM 3202
    10/9/12 7:18 PM 7289
    10/9/12 7:19 PM 192
    10/9/12 7:20 PM 9257
    10/9/12 7:21 PM 75
    10/9/12 7:22 PM 3099
    10/9/12 7:23 PM 83
    10/9/12 7:24 PM 93

    there should be only a count of 1 in this example because 4 consecutive values > 1000 occurs only once from 7:15 to 7:18.

    Can someone point me in the right direction?

    Thanks for your time.

  2. #2
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: count cells if ADJACENT row values meet criteria

    if your DateTime data are in cells A2:A24, and Value data are in B2:B24, use (in any other cell) the following ARRAY formula (see signature) to get the count like you desire:

    Please Login or Register  to view this content.
    or regular formula:

    Please Login or Register  to view this content.
    Last edited by icestationzbra; 12-10-2012 at 01:31 AM. Reason: fixed the formula
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  3. #3
    Registered User
    Join Date
    11-30-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: count cells if ADJACENT row values meet criteria

    This solution worked beautifully! Thanks very much!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1