# Posts by kant

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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

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

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

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)

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