Sumif if question

Steve Mount

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.

Mike Wilday

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

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

Steve Mount

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.

Mike Wilday

Are all of those checkbox columns next to each other? 

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

 

Mike Wilday

You might also try using Countif instead of Sumif. 

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

Paul Newcome

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.

Mike Wilday

Nice improvement Paul! 

Paul Newcome

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.

Steve Mount

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.

 

Paul Newcome

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

 

I completely forgot about this part. I'm glad we were able to help you find a solution.