SUM Providing Wrong Number

System

This discussion was created from comments split from: SUMIF value exists, leave blank if cells are blank (nothing to sum).

Alex Hackford

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

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

Alex Hackford

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!

Alex Hackford

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

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.