Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

SUMIF Circular Reference


I have a time/hours sheet to keep track of customers that have varying statuses. The statuses include in progress, complete, and cancelled. I currently have the sheet being sorted based on the status so that It has a title row showing the total numbers for each status below it. As a status changes from in progress to complete this moves the arrangement or order of the sheet which is what we want it to do. The issue we are seeing is that having a sumif statement check the entire column as it varies creates a circular reference when I have =SUMIF(Status2:Status400, "In progress", [Total Hours]2:[Total Hours]400) to account for the current total of the accounts in progress and =SUMIF(Status2:Status400, "Complete", [Total Hours]2:[Total Hours]400) to account for the current total that are complete. Each of these functions are on different rows in the same column but will have a changing number of rows beneath them as the status changes which is why it needs to reference the whole column. The circular reference comes because they are checking the same number of rows, but I feel like it should be able to check the entire column since the results will vary based on the status variable. Is there a better way to accomplish this?


This discussion has been closed.