If cell values match a string, add some values

Question

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.

Answer 1

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