SUM Providing Wrong Number

Answers

  • Help! I'm running into a similar issue where I have a column of cells with blanks that are summing incorrectly. Currently Smartsheets is returning '3' as the total of the January column which is clearly incorrect as the first cell in that column is '5'. I'm assuming this is because I'm returning blanks instead of '0' but I can't seem to figure out a way around it.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Alex Hackford How exactly are you pulling these numbers into this sheet and how exactly are they being populated on the source sheet(s)?

  • Hi @Paul Newcome ,

    The numbers are being pulled in from two separate sheets. The formula is as follows...

    =(IF(COUNTIFS({Hiring Plan Range 1}, Recruiter@row, {Hiring Plan Range 2}, 1, {Hiring Plan Range 3}, >=DATE(2021, 1, 1), {Hiring Plan Range 3}, <=(DATE(2021, 1, 31))) = 0, "", COUNTIFS({Hiring Plan Range 1}, Recruiter@row, {Hiring Plan Range 2}, true, {Hiring Plan Range 3}, >=DATE(2021, 1, 1), {Hiring Plan Range 3}, <=(DATE(2021, 1, 31)))) + IF(COUNTIFS({TA_Offer Decline data Range 1}, Recruiter@row, {TA_Offer Decline data Range 2}, $January$2) = 0, "", COUNTIFS({TA_Offer Decline data Range 1}, Recruiter@row, {TA_Offer Decline data Range 2}, $January$2)))

    I'm adding the # of offers made by a given recruiter + the number of offer declines seen by a given recruiter for each month of the year.

    On the source sheet the numbers are being calculated with the two formulas I'm adding above. So, # of offers made by a given recruiter is being calculated by

    (IF(COUNTIFS({Hiring Plan Range 1}, Recruiter@row, {Hiring Plan Range 2}, 1, {Hiring Plan Range 3}, >=DATE(2021, 1, 1), {Hiring Plan Range 3}, <=(DATE(2021, 1, 31))) = 0, "", COUNTIFS({Hiring Plan Range 1}, Recruiter@row, {Hiring Plan Range 2}, true, {Hiring Plan Range 3}, >=DATE(2021, 1, 1), {Hiring Plan Range 3}, <=(DATE(2021, 1, 31))))

    And the number of offer declines seen by a given recruiter is using

    IF(COUNTIFS({TA_Offer Decline data Range 1}, Recruiter@row, {TA_Offer Decline data Range 2}, $January$2) = 0, "", COUNTIFS({TA_Offer Decline data Range 1}, Recruiter@row, {TA_Offer Decline data Range 2}, $January$2)))

    Hope this helps!

  • You can disregard my issues above. I decided to remove the IF statement and just use conditional formatting to make the text color of a '0' white instead of relying on a blank. This seems to have fixed my issue and also helped me think of a better way to consolidate sheets instead of using multiple sheets to track individual KPI's.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm glad you were able to get a working solution. It is odd that your original was not though, as I use very similar solutions rather frequently with no issues.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!