Checkbox and IF statements
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
Comments

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.

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

You're welcome! I was like an IF statement with 31 different combinations???? No thank you!
That was a fun puzzle.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!