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

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.