How Do I select cells (with a checkbox) and sum them

[spamunch]

Hello,

I have a column of data for individual task times. for example task A takes 1 hr. Task B takes 2 hrs. Task C takes 4 hrs. I want to use checkboxes. When I click/select the checkbox I want the sum of all the selected cells to appear in another cell which I would call Total. how do I do that. Can it be done without using a macro?

I have no experience with VB, I've been studying since yesterday and it has a learning curve. If the solution involves VB please break it down simply for I'm simple man.

Thank you! Eagerly awaiting your response.






.

[Leith Ross]

Hello spamunch,

Welcome to the Forum!

I am assuming your check box is from the Forms toolbar and not the Control Toolbox. For this example A1 = Task A, B1 = Task B, C1 = Task C, and the check box is in cell D1. Let's say the link cell for the check box is G1 and the total cell is A3.

Total Cell A3 formula: =IF(G1=True, SUM(A1:C1), 0)

[spamunch]

Hello Leith, thank you for your answer. My checkbox was from the Active X control Would it matter? Also I meant to modify my post and make it more clearer. I have only one column and many rows
like this

A Total = 7
√ 1
√ 2
√ 4
10

Thank you.

[Leith Ross]

Hello spamunch,

The control toolbox check box is fine. Most people use the Forms controls since they are native to Excel and easier to setup. Just set the link cell property to "B1" and place this formula in "A5".

Total Cell A5 formula: =IF(B1=TRUE, SUM(A2:A4), 0)

[spamunch]

Leigh thanks I am making much progress. I set it up like you suggested =IF(B1=TRUE, SUM(A2:A4), 0) however when B1= false my grand total =0, What I would like is if B1=false then the value that task contributes to the grand total goes away rather than everything going to 0


For instance if A takes 20 minutes
B takes 30 minutes Grand total =50

However if I uncheck A I would like my grand total to become 30. I just dont know how to put in excel terms, that's why it took me so long to reply. Trying to figure it out.

I have a couple hundred checkboxes. Would I have to assign each checkbox to a link manually or is there an easier way?

Also from what I understand ( found on different forums) having a lot of checkboxes in my model is regarded as bad design. Is there a better way to accomplish what I want.

Thank you very much.

[Leith Ross]

Hello spamunch,

Sorry for the delay, I had a meeting to go to. The easiest method is to eliminate the check boxes and replace them using the Marlett font in the adjacent column. Typing a lower case "a" will produce a check mark in the cell. To remove the check mark just clear the cell. The next piece is to change the formula to a conditional sum using SUMIF.

Please Login or Register  to view this content.