SUMIF Circular Reference

Darian Nielson

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?

Brett Evans

Darian,

problems like this are easier to solve when we can see the actual sheet.  If you make a copy of your sheet, change or delete all of the customer names, then publish it, that will give us a lot more to work with.  After publishing it past the link back here and we can see it.

 

Thanks,

Brett

Darian Nielson

https://app.smartsheet.com/b/publish?EQBCT=234ae1c810e245c4beaa8fb7b20abfca

Brett Evans

I think you have a circular reference because your Phase rows are summing the same columns.

Solution:

1) Create and hide another column that Status + Left([Work Performed],4). 

1. 2) Adjust the criteria for the inprgress total to only pick up only work related line items, not summaries.
2. 3) Use a similar strategy for the completed total

Darian Nielson

That makes sense. I guess I had hoped that it wouldn't include the sum row because it didn't meet the status criteria. Thanks for the solutions.