I have a Spreadsheet that I want to send to a group of people, where each person has their own row and they choose what options they want with a checkbox (Using a Data Validation drop down containing either ☐ or ☑).

Basically what I want to do is check if an option is selected and use a value from elsewhere to calculate the total for each user. It is possible that more options would be added in the future so I need to be able to extend the options table.

I have an example sheet here.

I have already attempted it using the following:

= IF( AND( D3 = "☑" , J3 = 'DO NOT CHANGE'!B2, D3) , IF( AND( D3 = "☐" , J3 = 0)))

My hope was that this would assign J3 a value from the price list on a seperate page and then I could SUM the J column for the first user, K column for the second etc. and display the result in the Price cell for each. But this isn't working.


You can use arrayformula with SUM to do look up only the needed values like this:

=SUM(ARRAYFORMULA(if(B2:D2="☑", vlookup($B$1:$D$1,G:H,2,false),)))

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.