You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Anna Beekman Author

Level 1

4 points

Complicated formula for checkboxes?

Hey guys,

I've been making spreadsheets like this to track vacations and stuff for years, but I don't use spreadsheets enough to know how to write a formulae to automate this (these are all just manual calculations by myself). This spreadsheet is for an AirBnB rental.

How can I get the green numbers in the attached screenshot to automatically calculate based on checked boxes?

In this example, each cell in column J should be $589.26 ÷ the number of checked boxes in that row's columns B-H.

Then, each cell in cols B-H, row 9 should be the sum of the values of column J only where a checked box exists.

I would love to be able to just add/remove days for people who are still undecided, thus automatically updating everyone's total dues.

Hope this makes sense!

iMac 27", macOS 10.14

Posted on Jan 17, 2020 5:25 AM

Reply

Top-ranking reply

Level 7

26,589 points

Posted on Jan 17, 2020 5:38 AM

after looking at your screenshot I need to modify the formulas I suggested:

select the cell J2, then type:

=iferror(589.26/countif(B2:I2, true), "")

should be:

select the cell J2, then type:

=iferror($I2/countif(B2:I2, true), "")

View in context

Similar questions

Need a formula to have check marks control adding up cells in different column Trying to use numbers for a quick & dirty budget. I'd like a column of check boxes to control whether or not a different column's cells are totaled up. For example, C column is full of prices. D column is all check marks. I'd like the footer cell of D column to total up ONLY the cells in C column that have a checked box next to them in D column. I'm not the best with formulas yet, and this one is stumping me. Thanks!
421 1

5 replies

Top-ranking reply

Level 7

26,589 points

Jan 17, 2020 5:38 AM in response to Wayne Contello

after looking at your screenshot I need to modify the formulas I suggested:

select the cell J2, then type:

=iferror(589.26/countif(B2:I2, true), "")

should be:

select the cell J2, then type:

=iferror($I2/countif(B2:I2, true), "")

Reply

Jan 17, 2020 5:35 AM in response to Anna Beekman

select the cell J2, then type:

=iferror(589.26/countif(B2:I2, true), "")

shorthand for this is:

J2=iferror(589.26/countif(B2:I2, true), "")

to fill down, select cell J2, copy

select cells J2 thru J8, paste

Then, each cell in cols B-H, row 9 should be the sum of the values of column J only where a checked box exists.

B9=SUMIF(B2:B8, TRUE, $J$2:$J$8)

to fill across, select cell B9, copy

select cells B9 thru H9, paste

Reply

Anna Beekman Author

Level 1

4 points

Jan 17, 2020 5:49 AM in response to Wayne Contello

Wow thanks, we're making progress! Thank you sooooo much! The first step seems to work perfectly, however the second step there is an error:

Reply

Anna Beekman Author

Level 1

4 points

Jan 17, 2020 5:58 AM in response to Anna Beekman

Reply

Anna Beekman Author

Level 1

4 points

Jan 17, 2020 6:08 AM in response to Anna Beekman

Nevermind, I fixed it! Thank you so much!!!

Reply

Complicated formula for checkboxes?

Welcome to Apple Support Community

A forum where Apple customers help each other with their products. Get started with your Apple Account.

Learn more Sign up