# Sumif if question

Options
edited 12/09/19

Hey folks,

Working on a sheet to sum up if checkboxes are selected and running into an issue.

The formula I have now is- =SUMIF(PRD50, 1) + IF(Stories50, 1) + IF(Assets50, 1) + IF(Wires50, 1) + IF(Comps50, 1)

Each one of those are checkboxes and I want to tally them all up. Ideally, I'd like it to equal 100 since that would mean its 100% complete. But my current problem with that formula is that its not counting PRD50. Every combo of IF and brackets I have tried is not working. Any assistance appreciated.

Tags:

• ✭✭✭✭✭✭
Options

You need to change your IF's to Sumif like the first one.

=SUMIF(PRD50, 1) + SUMIF(Stories50, 1) + Sumif...

• Options

Thanks Mike. Now with all checkboxes ticked, its returning 0.

=SUMIF(PRD50, 1) + SUMIF(Stories50, 1) + SUMIF(Assets50, 1) + SUMIF(Wires50, 1) + SUMIF(Comps50, 1)

I did find a workaround by including an initial cell that is not necessary in the calculation, and a dropdown and does not include a number

=SUMIF(Priority50, 1) + IF(PRD50, 1) + IF(Stories50, 1) + IF(Assets50, 1) + IF(Wires50, 1) + IF(Comps50, 1)

Where Priority50 is not necessary. But its returning properly.

• ✭✭✭✭✭✭
Options

Are all of those checkbox columns next to each other?

Strange. Can I see a screenshot of what youre doing?

• ✭✭✭✭✭✭
Options

You might also try using Countif instead of Sumif.

https://help.smartsheet.com/function/countif

• ✭✭✭✭✭✭
Options

If all of the columns are next to each other you could use something along the lines of

=COUNTIFS(PRD@row:Comps@row, 1) / COUNT(PRD@row:Comps@row)

This will divide the count of the boxes that are checked by the total number of boxes which will give you a decimal which can then be converted into a percentage.

• ✭✭✭✭✭✭
Options

Nice improvement Paul!

• ✭✭✭✭✭✭
edited 01/10/19
Options

Thanks Mike!

I use the same setup to track attachments. I have them check a box once they attach the appropriate forms. I also use a JOIN(COLLECT to pull a list of everything that has not yet been attached and send out regular reminders until all boxes are checked.

• Options

Thanks for the suggestions! I ended up using countif in a couple different ways. One to tally up the checkboxes, a second column that tallied up "NA" if a checkbox was overwritten if that particular requirement was not necessary for that project:

=COUNTIF(PRD50:Comps50, 1) / COUNT(PRD50:Comps50)

=COUNTIF(PRD50:Comps50, "NA") / COUNT(PRD50:Comps50)

=SUM([Column25]50:[Column22]50) Made this a percentage to display the amount of prework completed in order to be ready for dev. Then hid the first two columns to clean it up.

• ✭✭✭✭✭✭
Options

a second column that tallied up "NA" if a checkbox was overwritten if that particular requirement was not necessary for that project: