Function Question
If a cell is check marked "✓" then I want a cell next to it to show the value of another cell. Then I want all subsequent cells underneath to run a value of that cell plus the value of the previous cell(s) only when checked. How would I create the formula for that please? For example if N3 is "✓" then M3 equals value of C3 for first formula. Second example, if N4 is "✓" then M4 equals the value of C3+C4. Then the third would follow suit with the value of M5 equal to the value of C3+C4+C5. And so on and so forth.
Answers
-
If a Checkbox is checked, you can use value = 1. If not checked, you can use value = 0. The structure you describe sounds like it relies heavily on the sorting on the sheet, which carries a risk: if the sorting is changed, there's a possibility the result will be inconsistent. To resolve this issue, I use a RowID autonumber column so each new row is numbered. So a formula could be something along the lines of the following. (Checkbox = your column N, Number Value = your column C, and RowID = the autonumber column that I suggest above.)
=SUM(COLLECT([Number Value]:[Number Value], Checkbox:Checkbox, 1, RowID:RowID, <=RowID@row))
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful", "Vote Up", and/or "Awesome" as appropriate.
