Why does the Sheet Summary SUMIFs formula show a different result than my report?

Char485

I am using this formula to report the total $ amount of past due projects, which rolls up to a dashboard for our team.

=SUMIFS(Fee:Fee, [Key Deadline]:[Key Deadline], <=TODAY(), Status:Status, "1-Pre FMV Info Request") + COUNTIFS([Key Deadline]:[Key Deadline], <=TODAY(), Status:Status, "2-FMV In Process") + COUNTIFS([Key Deadline]:[Key Deadline], <=TODAY(), Status:Status, "3-FMV Draft Delivered", [Coker Project Status]:[Coker Project Status], <>"04-Waiting on Info", [Coker Project Status]:[Coker Project Status], <>"11-Exec Sum Sent", [Coker Project Status]:[Coker Project Status], <>"12-Draft Report Sent", [Coker Project Status]:[Coker Project Status], <>"12.5-Draft Uploaded to PAWS", [Coker Project Status]:[Coker Project Status], <>"14-Revised Report Issued", [Coker Project Status]:[Coker Project Status], <>"15-Final Requested", [Coker Project Status]:[Coker Project Status], <>"16-Final Delivered", [Coker Project Status]:[Coker Project Status], <>"17-Final Uploaded to PAWS", [Coker Project Status]:[Coker Project Status], <>"18-Terminated", [Coker Project Status]:[Coker Project Status], <>"19-Complete")

I've set the same parameters in a report to double-check my work, but the report shows a much greater total ($240k more) than the Sheet Summary. After assessing both amounts, the report is correct and I need that total in my dashboard, but the formula, although showing as correct, is not resulting in the full amount. I'm not sure how to correct this.

DKazatsky2

Hi @Char485,

Give this a try.

=SUMIFS(Fee:Fee, [Key Deadline]:[Key Deadline], <=TODAY(), Status:Status, OR(@cell = "1-Pre FMV Info Request", @cell = "2-FMV In Process", @cell = "3-FMV Draft Delivered"), [Coker Project Status]:[Coker Project Status], AND(@cell <> "04-Waiting on Info", @cell <> "11-Exec Sum Sent", @cell <> "12-Draft Report Sent", @cell <> "12.5-Draft Uploaded to PAWS", @cell <> "14-Revised Report Issued", @cell <> "15-Final Requested", @cell <> "16-Final Delivered", @cell <> "17-Final Uploaded to PAWS", @cell <> "18-Terminated", @cell <> "19-Complete"))

Hope this helps,

Dave

DKazatsky2

Hi @Char485,

The formula looks like it is adding a SUM of fees to 2 COUNTS, is this really what you are intending to do? It seems you are looking to have a SUM of dollars as your total so I question the setup of the formula as COUNT does not provide a dollar amount.

Hope this helps,

Dave

Char485

Thanks for the insight, Dave.

The intention is to Sum the Fee column if the Key Deadline is today or in the past, when it equals Status 1,2, or 3, and where the Coker Project Status (separate from Status) is not equal to 4, 11, 12, 12.5, 14, 15, 16, 17, 18 & 19. Simpler formulas without the COUNTIFS included rendered unparseable and invalid operation errors, so with the help of Co-Pilot, this is what I came up with. Based on the limited research I've been able to do to this point, it seems there is some difficulty with including And / Or statements in one formula. Although it is possible, it seems that problems with the interpretation of the formula are an issue in Smartsheet. How would you write this formula? I will certainly try it to see if it throws an error. I've tried so many different variations with no luck.

DKazatsky2

Hi @Char485,

Give this a try.

=SUMIFS(Fee:Fee, [Key Deadline]:[Key Deadline], <=TODAY(), Status:Status, OR(@cell = "1-Pre FMV Info Request", @cell = "2-FMV In Process", @cell = "3-FMV Draft Delivered"), [Coker Project Status]:[Coker Project Status], AND(@cell <> "04-Waiting on Info", @cell <> "11-Exec Sum Sent", @cell <> "12-Draft Report Sent", @cell <> "12.5-Draft Uploaded to PAWS", @cell <> "14-Revised Report Issued", @cell <> "15-Final Requested", @cell <> "16-Final Delivered", @cell <> "17-Final Uploaded to PAWS", @cell <> "18-Terminated", @cell <> "19-Complete"))

Hope this helps,

Dave

Char485

Thank you so much, Dave! It worked! I appreciate the help so much. Have a wonderful weekend!

Charity