SUM result ignored by SUBTOTAL

[Ed Burke]

Hello all,

The problem: I'm running into an issue where the Excel SUBTOTAL function appears to be ignoring entries that it should not be ignoring. To the best of my knowledge, the SUBTOTAL (or AGGREGATE function by option) will ignore nested subtotals, but should include any values derived by any other functions. This is not the behavior I'm observing in my current project.

The setup: I have a "Chart of Accounts" that lists any account that a transaction can belong to. There are three account levels: Parent (level 1), Sub-Parent (level 2) and Child (level 3). Level 1 accounts can have Level 2 accounts assigned to them. Level 2 accounts can have Level 3 accounts assigned to them. Transactions are only assigned against level 3 accounts and the level 1/2 accounts are used to subtotal/summarize the various level 3 accounts. Clear as mud?

The formula: Here's a breakdown of the formula I'm using ... The first IF statement determines whether the account is a level 1 account based on the account numbering system. If it is a level 1 account, an OFFSET function is used to determine the range which should be SUBTOTALed for this account. If the first IF statement fails, the second IF statement determines whether the account is a level 2 account based on the account numbering system. If it is a level 2 account, an OFFSET function is used to determine the range which should be SUBTOTALed for this account. If the second IF statement fails, it is assumed this is a level 3 account and the SUMIFS functions kicks in to sum the total transactions assigned to this account.

=IF(AND(NOT(ISERROR(MATCH([@Account],[Parent]))),MOD([@['#]],1)=0),
SUBTOTAL(9,OFFSET([@CurrentBalance],1,0,SUM(IF(TRUNC(['#])=[@['#]],1,0))-1,1)),
IF(AND(NOT(ISERROR(MATCH([@Account],[Parent]))),MOD([@['#]],1)<>0),
SUBTOTAL(9,OFFSET([@CurrentBalance],1,0,SUM(IF(ROUNDDOWN(['#],1)=[@['#]],1,0))-1,1)),
SUMIFS(_Income[Total],_Income[To],"=" & [@Account])))

The attachment: I've stripped down the worksheet and included it as an attachment. On the "Chart of Accounts" sheet, the "CurrentBalance" column contains the formula. It should subtotal all accounts that have an account number that start with 1.x. The CurrentBalance for Account 1.1 is derived from the formula above. The CurrentBalance for Account 1.2 is derived from an extracted portion of the formula above (only the SUMIFS part, see Experiment 2 below). You'll notice that the CurrentBalance from Account 1.1 is not included in Account 1.0's subtotal, but the CurrentBalance from Account 1.2 is.

The result: Individually, each portion of this formula works exactly as I intended, but here's the rub ... any results derived by the SUMIFS portion of this formula are treated as nested subtotals and ignored by level 1 and level 2 accounts attempting to subtotal them.

The experiments:
1) I've replaced SUMIFS with SUM(), SUM(IF), and SUMPRODUCT, all with the same result (no help).
2) For level 3 accounts, I deleted the entire formula except for the SUMIFS portion. This produced the intended results and the subtotals for level 1 and level 2 accounts worked perfectly. The only problem with this solution is that, at runtime, I won't know if the account is going to be level 1, 2 or 3 without first going through the checks that I just deleted, so it won't work as intended on level 1 or 2 accounts (no help).
3) I removed all SUBTOTAL functions in the formula and replaced them with SUMs. This allowed the level 3 account totals to be added correctly, but incorrectly included subtotals, causing double-counting (no help).
4) I replaced all SUBTOTALs with AGGREGATEs. Using ignore options 0 and 4, I was able to determine that Excel is in fact treating all results obtained with the original formula as nested subtotals. Doesn't help, but at least I know what's going on now.
5) I re-arranged the formula to put the SUMIFS as the beginning of the formula (no help).

The question: So, with all that information, does anyone have any idea why Excel is treating the results from the SUMIFS statement as nested subtotals when that SUMIFS function is included in the same formula as a SUBTOTAL, even if the SUBTOTAL portion isn't being used?

 

[r2c2]

@Ed Burke

Interesting problem. I am not sure how SUBTOTAL determines which other cells have formulas are also SUBTOTALS. I guess, the logic must involve some FIND() in cell.Formula

Anyhow, I think you can answer the question in a simpler way with two SUMIFS() one running on _income table and another on [CurrentBalance] column.

Like this:

=IF(ISNA(MATCH([@Account], _Income[To],0)), SUMIFS([CurrentBalance],[Parent],[@Account]), SUMIFS(_Income[Total],_Income[To],[@Account]))

We just check if the [@Account] is a valid code in _Income[To] and if so, we run sumifs on that.
Else, we just sum up all [CurrentBalance] values where [Parent] matches [@Account].

Although this is technically a circular reference, Excel is not warning me. So all good.

Let us know if this solves the problem

 

[Ed Burke]

R2,

Thanks for the response. Seems logical. I haven't tried it yet, but I think there may be a problem with the first SUMIFS because another row in the CurrentBalance column can contain a subtotal (i.e. for a Level 2 account, which was not in the example spreadsheet), thereby creating a double-count error.

I'm not near the spreadsheet right now, but I'll give it a shot tomorrow morning and update the answer.

Thanks!
Ed

 

[r2c2]

@Ed Burke

Theoretically it shouldn't as we just add up data from cells that have current cell as parent. Please note that in Excel tables, it is not recommended to have different formulas in different rows. So use one formula all along and it will work as expected.

 

[NARAYANK991]

Hi ,

A similar solution is uploaded ; see if you can use it in your working file.

To avoid a circular reference error , I have set the Iterations to 1 in Excel Options.

Narayan

 

[Ed Burke]

All,

Thanks for the help.

R2, The whole reason I asked the question is because I wanted to use the same formula throughout the column. Having the function SUBTOTAL anywhere in the formula, apparently, completely prevents that. Still can't find a reference to how Excel determines what is and is not a subtotal, but I'll just move on.

Narayank991, Thanks for the input. The formula appears to work, but I can't send out a workbook that requires users to change their options. It's just a limitation I have in my current corporate environment. Also, when I add the additional portions (checks and balances) of the formula, it crashes.

I solved the issues by creating a separate calculation for summations and subtotals and using a formula to determine which one to display based on the type of account.