Forum Discussion
Inserting one checkbox and linking to multiple cells to trigger sums
I apologize SergeiBaklan . Apparently, the right sheet did not attach. Here is the correct one that I was referencing to in the question above. Thanks!
SergeiBaklan If it is different I can change the 8 in this formula to the number of rows; however I am only needing a sum formula for this example. Any ideas?
This formula works if each block has exactly 8 rows. But how Excel knows that one block has 8 rows, another one 3 rows, next one 5 rows, etc. ?
=INDEX($A:$A,INT((ROW()-ROW($A$3))/8)*8+ROW($A$3)) this was the formula in my conditional formatting to identify that rows 3:8 were together. Don't know how I write this to indicate they are in the sum once the A3:A8 is checked to be "true"
It depends on how Exel knows that from row 3 it shall sum till row 8 and from row 10 to row 11. Other words, which formal logic do you use to separate the blocks.
What would an equation be then to sum rows 3:8 if true and also add in rows 10:11? SergeiBaklan
That's since your formula
=SUMIF(A3:A11,TRUE,J3:J11)check if any of A3:A11 is TRUE and sum based on this. Actually if A3 is true you shall sum rows 3:8 since in A3 is check box for entire block. That could be formula like in conditional formatting rule to check entire block.
In your latest sample I see that the block could be of different size, that complicate the task. If so, we need to have some formal logic to recognize how many rows in the block, so far I don't see it.
Thank you!SergeiBaklan Do you happen to know why my formula for the total is not adding up all the numbers when the boxes are checked? Looks like it is only calculating the top cell and bottom 2 vs. all. Thanks!
It's hard to test on only one block, perhaps rue formula is
=INDEX($A:$A,INT((ROW()-ROW($A$3))/8)*8+ROW($A$3))