Excel Subtotal Returning 0

[hogdriver]

I'm creating a custom solution to track basic accounting data for a 20-person organization. I have a listing of accounts that includes primary (parent) accounts and sub-accounts. Each of these accounts can have child accounts (for example, Account 6 is a child to Account 4, which, in turn, is a child to Account 1). In this construct, Account 1 would be purely a parent account, Account 4 would be a sub-parent (parent + child), and Account 6 would be purely a child account. Only purely child accounts have transactions assigned to them on the transactions spreadsheet.

I'm trying to display subtotals for each of these accounts and, for some unknown reason, keep getting a zero subtotal.

The table columns include #, Account, Parent, CurrentBalance. The first parent account # is 1.00. The first sub-parent to this account is 1.50 and the first child to 1.50 is 1.51. The following formula will correctly determine if the account has children and correctly determine the appropriate range to subtotal. If the account is a child-only, this formula defaults to the final SUMIFS statement to sum the transactions from a table on another spreadsheet. Using this formula always returns the correct transaction totals, but always leaves the subtotals at zero.

Please Login or Register  to view this content.

If I extract the SUMIFS statement (purely copy and paste, no modification), the child-only account summation becomes:

Please Login or Register  to view this content.

Using this method, the parent account correctly subtotals. I realize I could use this as a solution, but I need one formula for all of the cells in the subtotal column because accounts can be added/deleted/changed at any time.

If the first formula correctly shows the child's total, any idea why it doesn't factor into the parent subtotal? My first thought is that the formula is ignoring the child's summation because it considers it a subtotal itself, but if this was the case, why would the extracted SUMIFS statement allow the subtotals to calculate correctly? Also, I've tried trading out the SUMIFS formula for a SUMIF, SUM(IF) and SUMPRODUCT, all of which returned the correct results, but none of which would be included in the parent account subtotal when included in the larger formula.

Thanks in advance for any help you can provide.

[FDibbins]

Hi, welcome to the forum

It can be really hard to trouble-shoot a formula with no context to it, and this can be made even worse with structured table references, so I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[hogdriver]

Ford, thanks for the quick reply. The spreadsheet is at work, so I'll try to clean it up and see if I can get on the site through the firewall. If so, I'll upload it tomorrow morning.

[FDibbins]

Have you tried using sumifs() for the rest of what you want?

[hogdriver]

Have you tried using sumifs() for the rest of what you want?

I've tried using every variation of SUM I can think of ... SUMIF, SUM(IF...), SUMIFS, SUMPRODUCT, etc. They all return the result I want until I include them in the larger formula. Once in the larger formula, they return the correct result, but are ignored by the SUBTOTALs.

[FDibbins]

subtotal will only ignore other ST's

If they work on their own as sumifs(), perhaps you can just combine them that way?

[hogdriver]

Ford,

Thanks for the feedback. Not sure if that method will meet my requirements, but I'm still experimenting. Can't seem to attach the workbook, so working through that now.

[hogdriver]

Ok, I think I've got the workbook attachment working. In it you'll find the first worksheet ("Chart of Accounts"). There are five accounts setup on this table: 2 parent-only and 3 child-only. On the "Income" worksheet, there are two entries relating to Accounts 1.1 and 1.2. On the "Chart of Accounts" sheet, these values are correctly calculated in the "CurrentBalance" table column. However, you can quickly see that only one of these values is being added into the Account 1 subtotal. The value in Row 3 is not being added (it uses the long formula) while the value in Row 4 is being added (uses the extracted formula).

[hogdriver]

Quick update... I've been doing some experimenting with the issue. I've found that if I replace the SUBTOTAL functions with any other function, the SUMS add correctly into the parent account subtotal. Still can't figure out why, but I'll keep on the issue.