Conditionally sum values at each interval of change in other cell

  • Hello,

    I'm working on a sheet of this type:

    A1 B1 C1
    Value Signal Switch
    123 Off
    132 Off
    144 Off
    123 On On
    134 On
    165 On
    345 On
    345 Off Off
    765 Off
    342 Off
    123 Off
    133 On On
    134 On
    177 On
    132 Off Off
    145 Off

    Along the sheet, column B switches several times from "On" to "Off". I did isolate the signal getting column C ("Switch"). I need to perform some simple operations (as AVERAGE, MIN, MAX, etc) on each "strips" marked by "ons" on column B, that is, I need a formula that applies to values in column A within "On" and "Off" in column C.

    For Instance, I need the minimum for each strips (that is 123 for the first strip; and 133 for the second one)

    I hope I made myself clear.

    Thanks a Lot


  • Re: RANGE Until VALUE"XXX" Changes to "YYY"

    Hello again...

    I'll try to explain this in another way (as everybody can see English is not my first language...)

    John is a simple robot-farmer. He performs just two tasks: working and sleeping.
    When John is awake he picks up strawberries in his field. When he's asleep the wild birds eat them.
    John gets tired quite randomly: sometimes he can work for 14 hours in a row or more, sometimes he falls asleep after one or two hours.

    In column A you find the number of strawberries picked up by John (when he's awake) or eaten by the birds (when John's asleep).
    Column B shows when John is awake.

    A B
    133 sleepin'
    234 sleepin'
    342 awake
    571 awake
    345 awake
    343 sleepin'
    234 sleepin'
    564 sleepin'
    323 sleepin'
    234 awake
    113 awake
    123 sleepin'
    (in this example: during the last 12 hours, John slept for two hours, then he worked for 3 hours and fell asleep again for 4. He woke up and worked for two more hours, and then fall asleep again).

    John needs a formula to tell him (every time that he falls asleep) how many strawberries he picked since he's last awakening.

    (in the example, the formula should give 342+571+345 when John falls asleep the first time, and 234+113 when John falls asleep the second time.)

    Please help!

    Thanks a lot!

    kant (and John)

  • Re: RANGE Until VALUE"XXX" Changes to "YYY"


    If # of srawberries is in A1:A7 and awake/sleepin' is in B1:B7 then this will do it:


    Looks in B1:B7 for "awake' and sums the corresponding numbers in Col A

  • Re: Conditionally sum values at each interval of change in other cell


    Please note the change made to your thread title and in future threads try to give your thread a title that more accurately describes the content of the thread. Your thread title should be "search friendly", meaning a search using YOUR title as the search terms would yield relevanr results. Thanks.

    [COLOR="blue"]Why not use the SubTotal feature and set it sum the values at each change in awake/sleep?[/COLOR]


  • Re: Conditionally sum values at each interval of change in other cell

    Thanks rbrhodes,

    Unfortunately, your post still does not answer my question: "SUMIF" gives the total # of strawberries that John collected during the entire period, while I need the two partial sums he collected during the two working shifts in the example.

    Thanks for helping


  • Re: Conditionally sum values at each interval of change in other cell

    rbrhodes is correct. Kant, what you will need to do is place =SUMIF(B1:B7,"awake",A1:A7) under your data and then place =SUMIF(B1:B7,"sleepin",A1:A7) under the first code. These formulas only return the totals of "sleepin" and "awake". You can also add =sum(A1:A7) in there to tally your grand total. Hope this helps.

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!