Subtotal doesn't work properly

[Siepe1990]

I've used subtotal a lot and never has it failed me. However, now I am lost. Here's my data-set (simplified for example):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]With Formula[/TD]
[TD]Flat Data[/TD]
[/TR]
[TR]
[TD]Value[/TD]
[TD]10.000[/TD]
[TD]10.000[/TD]
[/TR]
[TR]
[TD]Value[/TD]
[TD]10.000[/TD]
[TD]10.000
[/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD][/TD]
[TD]20.000
[/TD]
[/TR]
[TR]
[TD]Value[/TD]
[TD]10.000[/TD]
[TD]10.000[/TD]
[/TR]
[TR]
[TD]Value[/TD]
[TD]10.000[/TD]
[TD]10.000[/TD]
[/TR]
[TR]
[TD]Value[/TD]
[TD]10.000[/TD]
[TD]10.000[/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD][/TD]
[TD]50.000[/TD]
[/TR]
</tbody>[/TABLE]

The formula I use is the following (cell B2):

=IF($A2="Value";5*2000;SUBTOTAL(9;$B$1:B1))

It gives 10.000, like it should. Next, cell B3:

=IF($A2="Value";5*2000;SUBTOTAL(9;$B$1:B2))

Nice, now B4 should give me the total of the 2 rows above. However, it doesn't...

Now, if I put the value (10.000) as a hard copy, the formula works all of a sudden...

Anybody where who can help me out?

 

[SpillerBD]

SUBTOTAL appears to be working right to me. It only ignores the other cells with a SUBTOTAL function.
With the $B$1 being fixed, you'll always catch the equivalent of total through..
So its always ignoring your formula because your IF... formula includes a SUBTOTAL function.

 

[pgc01]

Hi

Subtotal is working OK. Like SpillerBD said Subtotal() ignores other subtotal() results, that's why you are getting the 50.000 you want in B8 and not 70.000 that would be the sum of all the values above.

My question is

...Next, cell B3:

=IF($A2="Value";5*2000;SUBTOTAL(9;$B$1:B2))
...

why would you use Subtotal() in B3?

 

[Siepe1990]

Thanks for the reactions both of you. I think my explanation is not quite clear enough - struggling with explaining what I want - so I've uploaded the sample file:

https://gofile.io/?c=V380wa

As you can see in column B, the subtotal() doesn't add up to 20.000 (the first Result-row) or to 50.000 (the second Result-row).

Reason why I want to do it like this is that I have a large dataset (weekly changing as well) where 'Result' lines are mixed with 'Value' lines.
If it is a 'Value' line I want it to perform a calculation (more difficult than in the example I shared) ; if it is a 'Result' line, I want it the calculate the sum of the lines above but ignoring the other 'Result' lines. I figured subtotal() should do the trick easily, but somehow it doesn't work...

 

[AlanY]

this is how the subtotal() works, it will ignore other cells that with subtotal() to avoid double counting.
as the cells in Column B are all with subtotal(), that wouldn't work as it stand

try this

Book1
	A	B
1		With Subtotal
2
3	Value	10,000
4	Value	10,000
5	Result	20,000
6	Value	10,000
7	Value	10,000
8	Value	10,000
9	Result	50,000
Sheet1

Cell Formulas
Range	Formula
B3	=IF(A3="Value",5*2000,SUMPRODUCT(--($A2:A$3="Value"),$B2:B$3))

 

[pgc01]

Hi

Try in B2:

=IF($A2="Value",5*2000,SUMIF($A$1:A1,"Value",$B$1:B1)-SUMIF($A$1:A1,"Result",$B$1:B1))

or, same thing a bit shorter

=IF($A2="Value",5*2000,SUM(SUMIF($A$1:A1,{"Value","Result"},$B$1:B1)*{1,-1}))

Copy down