Sumif if question
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.
Comments

You need to change your IF's to Sumif like the first one.
=SUMIF(PRD50, 1) + SUMIF(Stories50, 1) + Sumif...

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.

Are all of those checkbox columns next to each other?
Strange. Can I see a screenshot of what youre doing?

You might also try using Countif instead of Sumif.

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.

Nice improvement Paul!

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.

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.

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.
Help Article Resources
Categories
Check out the Formula Handbook template!