Sumifs with Checkboxes

crwelch

I have a worksheet that I am using for adding event entries in via a form. The entries will check the boxes of the events they want to enter. Each event is a different cost. I want to do a final column for each person entered that will add up each row based on the events given $ amount if the box is checked. 

 

Events:    PWB     JrB      SrB    CR     SR     LBDR    DR     GTU     GTT     WR    2CS

Costs:      10          20        20      20     20       5            10       5            5         20      10

 

i.e.             x                                                      x                       x                         x                =$40

 

Thank you

Andrée Starå

Hi,

This is one way to do it.

Try this,

Add one extra column for each event and then a total that will total all the events checked. (you can hide the extra columns if you'd like)

The extra column will show $0 if not checked and the event price if checked.

The total column will sum each row (each person)

Formulas:

Formula: =IF(PWB@row = 1; 10; 0) 

The 10 in the above formula is the price for the event so you will have to change that for each event and then fill down the formula in the column.

Formula: =SUM(PWB@row:[SR$]@row)

The range is just an example so you will have to update it to include all the events and then fill down the formula in the column.

Please see the attached link/screenshot for more information.

Sumifs with checkboxes

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work for you?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Andree's suggestion will work, however...

 

Another option would be to change it from Checkboxes to Dropdowns with a Yes/No option. In the form, you can set the default value to be No, so that whoever is filling it out only needs to change whatever events they want to attend to Yes.

 

In the top row, put your values. The second row would be the first form entry.

 

In your Total column you can use:

 

=SUMIFS($PWB$1:$[2CS]$1, PWB@row:[2CS]@row, @cell = "Yes")

 

This will add up the values that you have in row one for whatever "Yes"'s there are on the row of the submission.

 

This will only require one extra row and some very quick adjustments to your column formats and form. It will also be only one formula as opposed to having to write a new one for each event.

Andrée Starå

Paul,

That's much better. How could I have missed that