# 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

Kant

• Re: RANGE Until VALUE&quot;XXX&quot; Changes to &quot;YYY&quot;

Hi,

Actually not.

• Re: RANGE Until VALUE&quot;XXX&quot; Changes to &quot;YYY&quot;

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.)

Thanks a lot!

kant (and John)

• Re: RANGE Until VALUE&quot;XXX&quot; Changes to &quot;YYY&quot;

Hi,

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

=SUMIF(B1:B7,"awake",A1:A7)

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

kant,

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

## Files

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/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

Kant

• 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]

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

Subtotal will do what you ask. See example attached to my previous post.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

## Participate now!

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