Numbers CheckBox and Sum to existing value

Question

RajKG Author

Level 1

8 points

Numbers CheckBox and Sum to existing value

Per attached screenshot, Column K has 'Sold' checkboxes.

If I check K7 checkbox, I need """Actual Investment (I7)+Profit Cash(P7)""" to be added to existing value of column D at LAST ROW i.e, 'D9' per screenshot. D9 = [$363.01+$60.00+$1.50] = $424.51]
If I check K8 checkbox, I need """Actual Investment (I8)+Profit Cash(P8)""" to be added to existing value of column D of LAST ROW i.e, 'D9'= [$363.01+$60.00+$2.00] = $425.01]
If I check K9 checkbox, I need """Actual Investment (I9)+Profit Cash(P9)""" to be added to existing value of column D of LAST ROW i.e, 'D9'= [$363.01+$52.00+(-$1.00)] = $414.01]
-----Any 'Sold' checked, corresponding ''Actual Investment+Profit Cash'' must be added to Column D of last row

How do I achieve this?

Posted on Oct 16, 2019 10:32 PM

Reply

Answer 1

t quinn

Level 5

7,063 points

Oct 16, 2019 11:22 PM in response to RajKG

Hi RajKG,

How is Rem Balance being calculated now? Any function to add to that will be in addition to current formula.

D9= <previous calculation> +SUMIF(K,TRUE,I)+SUMIF(K,TRUE,P)

If the values in column D are not a product of a function, them you need a different cell for the calculation.

As a note, merged cells can be trouble in Numbers. They tend to make things difficult in a variety of ways. You can get the same effects (or close to it) by adjusting the cell border styles and the text alignment.

quinn

Reply

Answer 2

RajKG Author

Level 1

8 points

Oct 17, 2019 9:46 AM in response to t quinn

Thank you Quinn for your response.

Hope I am not complicating my ask here and below are my comments and more asks --

All cells in D column have preexisting formula of IF condition and simple math

For instance, D9=IF(AND(B9=1,K9),C9+P9,A9×(1−B9)+C9−I9)

From your screenshot above, Lets say -

If a new row 10 is added to table, and then checked K7 -

D10= <preexisting calculation> +SUMIF(K7,TRUE,I7)+SUMIF(K7,TRUE,P7) AND keep the value of D9 as is, i.e 1414 and update D10 = D10(existing value)+I7+P7

Then if D4 is checked -

D10= <preexisting calculation> +SUMIF(K4,TRUE,I4)+SUMIF(K4,TRUE,P4) AND keep the value of D9 as is, i.e 1414 and update D10 = D10(existing value)+I4+P4 --- At this point, D10(existing value) should being having "D10(existing value)+I7+P7" achieved from previous step
If a new row 11 is added here and any other checkbox is TRUE(example, K6), then add corresponding K6+P6 value on D11 to its preexisting calculated value.

Reply