Using IF statement with Subtotal Result - nested subtotal not working

This is a "nested" subtotal problem.

First of all - yes I know that subtotal will not include other subtotal functions in the same column.

The main issue is that I am using a Subtotal function as part of an IF statement, and it's not adding up the non-subtotal part of the IF.
If I change the Subtotal to be a SUM then it works as expected.
I think, Excel is seeing the subtotal function anywhere in the formula and ignoring the entire cell.


This is the formula in cell L5:
=IF(AND(E5<>"",F5="Yes"),E5,SUBTOTAL(9,L6:L11))

Explained: IF (E5 is not blank, and F5 is YES) then use the value in E5, if false, then subtotal L6:L11.

to reproduce the problem create a subtotal of L4 to L12.
It will not include the value in L5 if the statement is true.

In layman's terms: If the user sets F5 to Yes, use the allowance amount in E5, if not calculate the subtotal for the lines below.


I am assuming this is expected, but in all my years of Excel formulas, I've never seen this.

Is there a way around this without using a 2nd column, to gather the amounts and total there?
I have proved that if I use a 2nd column and just say that column = the column I want to total, it will work as needed.
But that is a rebuild of the entire design.


Regards,
Sepp.

I'm just catching up on old threads.

I saw your solution, but this would add the subtotal amount and the estimated amount, which is not the desired result.
It's an either/or condition.
Either use the estimated amount, or use the actual subtotal amount, but don't add the two together.

In the end, we abandoned the logic of using an estimated total and moved the estimating into the detail of each section so that the subtotal formula is not conditional.

Glenn, your response makes no sense.

A> The question was answered - using subtotal anywhere in a formula prevents it from being added in other subtotals.
B> The issue is moot as I changed the logic for my spreadsheet.

No need to post a workbook or anything else.

However, If I post in the future, I'll keep that in mind.

Have a great summer.