SumIF/CountIF not Working

Help please... why is it that my formula is returning a "0" zalue in Timecard Completed1 field when it should technically return a "1"?

Formula being used: =SUMIF(($PPE$10:$PPE$172), $PPE@row, ([Timecard Complete Helper]$10:[Timecard Complete Helper]$172))

I'm trying to get the 'Timecard Completed' column to count the total number of Employee's that Completed their timecards in a specific Pay Period. NOTE: The formula works if I hardcode the number "1" in the individual line items (e.g., [Timecard Complete Helper]12)

FYI: 'Timecard Complete Helper' rows are calculating based on the value in the 'Timecard Completed' column with the formula =IF([Timecard Completed]@row = "Yes", "1", "") - Ideally, I'd rather it be running of a Checkbox instead.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @RRIOS

    The formula in your 'Timecard Complete Helper' column is returning text instead of a number, which is why your SUMIF is unable add these values together!

    If you want a number 1 to be returned, remove the "quotes" around it, like so:

    =IF([Timecard Completed]@row = "Yes", 1, "")

    If you want the Timecard Completed column to be a checkbox column, then you can use the same formula to check the box! I would use 1 and 0 though, like so:

    =IF([Timecard Completed]@row = "Yes", 1, 0)

    However keep in mind that then in this case you can't use SUM since there are no numerals to SUM. Instead, you'll want to use COUNTIFS to count how many times a checkbox appears:

    =COUNTIFS($PPE$10:$PPE$172, $PPE@row, [Timecard Complete Helper]$10:[Timecard Complete Helper]$172, 1)

    Let me know if this makes sense and if this works for you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!