Hi All,
I have a question I was hoping someone could please help me out with!
I have three consecutive columns. The first column has numbers that can repeat, but are always sorted from low to high. The second column will always have a 0 or 1 in it (formatted as a Yes or No). The third colmum contains numbers I would like to sum.
The conditions are: For every individual number in the first column, I want to: (i) sum all the numbers in the third column which have 0 (No) next to them in the second column, (ii) stop the sum in the row before the number in the second column switches to a 1 (Yes), (iii) restart a new sum every time it shifts back to 0 (No) again, and (iv) if it ends on a 0 (No) before the number in the first column changes, do one final sum of all the remaining 0s (Nos).
I think I am most of the way there with the following formula (the only issue I am having is that it's including 1s (Yeses) in the sums): =IF(AND(L3=0,OR(L4<>L3,K4<>K3)),SUM($M$2:M3)-SUM($N$1:N2),"").
I imagine this might get confusing, so I'll do my best to help by attaching the spreadsheet. The three columns are K, L and M, and I'm trying to write the formulas in column N.
Thank you!!!!
Bookmarks