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.

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Sheet Countdown Timer
Happy Thursday. I know many have asked this question before, but does anyone know of a way to have a column actively countdown time? Even if a plugin is needed, it will be considered. Here is what I'm looking to do. When a form is filled out and a new line is added to the sheet, I'd like the "Countdown Timer" column to…
Formula for populating date
I am needing a formula that calculates Termination Date (date property). The formula needs to look at Initiation Date (date property) and Term (text/num) which is based on # months, to calculate Termination Date. In this example, the termination date should return a value of 1/01/26. Thank you!
INCORRECT ARGUMENT SET on an =IF function
I'm trying to pull in the contact information of a second HR Business Partner, contingent on the department of the individual, from a separate reference sheet. However, not all departments have a second HRBP— so if the column of "second HRBP contact info" is blank, I don't want anything to be pulled into the new sheet's…