Sumifs with Checkboxes
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
Comments
-
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.
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
Paul,
That's much better. How could I have missed that
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!