Subtotals without parent/children rows?

Keith V

Hello,

I'm working on a sheet where rows are continuously inserted using a web form so I can't use parent/child functionality.  These rows contain hours spent on clients and sub-clients.   I have two summary rows at the top of the sheet and I'd like the hours column on those two rows to have the total hours for clients (row 1) and sub-clients (row 2).  I'm trying the following formula for row 1 hours:

=SUMIFS([Hours]:[Hours], [Client/Sub-Client]:[Client/Sub-Client], "Client")

I get the totals for summary line one perfect, however I get a circular reference error when applying the following formula to summary line 2:

=SUMIFS([Hours]:[Hours], [Client/Sub-Client]:[Client/Sub-Client], "Sub-Client")

The only way I've been able to make this work is to add an extra column for use only in the summary rows called "Total Hours".  Then I can use the two formulas.  

Since adding extra columns for each item I want to total is going to drastically increase the size of the sheet, is there any way to keep using the Hours column and get two separate totals by client type?

Mike Wilday

There are probably two issues at play here... 1) You probably have the client/subclient name in your summary row - and the formula in the hours row. Based on your formula, it appears that you are surveying the entire column, which is good for making sure the query grows with your spreadsheet. However, if you have the client name in the summary row it will be trying to include the hours from your formula, thus creating a circular reference. Make sure your Client/SubClient name in the summary rows are unique from how you are referencing them in the rest of the sheet. I.e. Add summary after the client name... "Client Summary" would not count that in your reference. 

Does that make sense? 

Keith V

You explanation makes sense.   I have the primary column holding the client names.  In the summary rows they are set to "Client Summary".  The next column is the one I am using to determine how to total the hours.   I changed those to be unique on summary rows, however I still get a circular reference when I take the formulas out of the Total Hours column and apply them to the hours column.   Please see attached screenshot.  Maybe I'm still missing something?

Mike Wilday

Can you copy and paste the formula directly from that one cell that's getting the circular reference? There has to be something in there that is counting upon itself. 

 

Keith V

The error cell formula is:  =SUMIFS([PDD Hours]:[PDD Hours], [Client/Sub-Client]:[Client/Sub-Client], "Sub-Client")

The problem seems to be that I can't have two SUMIFS in two cells in the same column referencing the entire column.   If I blank out both cells in PDD Hours, then paste the formula above first, it will work until I paste =SUMIFS([PDD Hours]:[PDD Hours], [Client/Sub-Client]:[Client/Sub-Client], "Client") in the cell above it.

But both formulas live happily together in the Total PDD Hours column.  

J. Craig Williams

Keith,

Your analysis is correct. You cannot have two SUM/SUMIF/etc... referencing themselves. This is because the first could* return a value that the other wants to add to its sum and the second could* return a value that the first wants to add to its sum ... but since the system is not sequentially determined, they would both be 'correct' and the cycle would continue.

I see you have several blank cells in the image provided. I would suggest moving the formulas there instead of adding more columns. I often build Sheets that have consolidation areas in them that take advantage of unused cells. Just lock the whole row to prevent them from being overwritten by wayward editors.

I hope this helps.

Craig

* I say could because the logic for circular reference is done 'at design time' as opposed to 'at run time' when your criteria would prevent the calculation in either cell from impacting the other.

 

Mike Wilday

Yep. That's the problem. You're searching the whole column and its a circular reference. If you move them over a couple columns that should fix the issue.