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
Question marked as ⚠️ Top-ranking reply

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), "")


5 replies
Sort By: 
Question marked as ⚠️ Top-ranking reply

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

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

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.