Checkbox and IF statements

briparke@

Hello having trouble creating a formula and not sure if it is possible

So I have two sheets first with 5 check boxes and then a value field. The second which holds the values.

I want to be able to check the boxes and then have the value field add up the total

example

Sheet 2 values = These values might change over time hence why I want reference them.

V1 = 2  V2 = 6  V3 = 20  V4 = 55 V5 = 35

Sheet 1 actions

If check CB1+CB2+CB5 I would get a value of = 43

 

I can get 1 value to work but it seems I can only reference sheet2 once in the the formula and only in one column

=IF(CB1 = 1, {SHEET2 Range 1}, IF(CB1 = 0, 0))   This works

=IF(CB1 = 1, {SHEET2 Range 1}, IF(CB1 = 0, 0)) + IF(CB2 = 1, {SHEET2 Range ???}, IF(CB2 = 0, 0))  Cannot add the second range.

 

I would want something like this just not sure if it possible

 

=IF(CB1 = 1, {SHEET2 V1}, IF(CB1 = 0, 0))+IF(CB2 = 1, {SHEET2 V2}, IF(CB2 = 0, 0))+IF(CB3 = 1, {SHEET2 V3}, IF(CB3 = 0, 0))+IF(CB4 = 1, {SHEET2 V4}, IF(CB4 = 0, 0))+IF(CB5 = 1, {SHEET2 V5}, IF(CB5 = 0, 0))

 

Hopefully I have explained this right and can be done

 

-Brian

 

Mike Wilday

This is totally doable. At first I was going to recommend using helper columns. But this is totally possible by summing 5 different IF statements. I have 5 checkbox columns labled one, two, three, four, and five. Then I sum 5 different if statements that add 0's if the checkbox isn't checked or the total of te checkbox if it is checked. Worked like a charm. 

=SUM(IF(one@row = 1, 2, 0), IF(two@row = 1, 6, 0), IF(three@row = 1, 20, 0), IF(four@row = 1, 55, 0), IF(five@row = 1, 35, 0))

Check out this sample video showing how its done.

You can replace one, two, three, four and five with your own column names and update the values of each checkbox if I got it wrong.  

briparke@

Thank you for the solution I knew there was a more elegant solution to the hack I did!

 

Mike Wilday

You're welcome! I was like an IF statement with 31 different combinations???? No thank you! 

That was a fun puzzle.