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.

📰 Apple Fitness+ unveils an exciting lineup of new ways to stay active and mindful in 2025

Offerings include new programs for strength, pickleball, yoga, and breath meditation, and a new collaboration with Strava. Learn more >

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!
424 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