Subtotal formula Subtotals greater than zero needed.

[Fin Fang Foom]

Is there a Subtotal(9, formula that it will only Subtotal greater than zero?
I tried this but does'nt work.

Any Ideas?

=IF(SUBTOTAL(9,S3:S86)>0,SUBTOTAL(9,S3:S86),0)

 

[PaddyD]

q: are you using subtotal() because you want to ignore filtered cells?

 

[Fin Fang Foom]

Yes, just to ignore filtered cells and also ignore the subtotals that are negative.

Is this possible?

 

[Yogi Anand]

Is there a Subtotal(9, formula that it will only Subtotal greater than zero?
I tried this but does'nt work.

Any Ideas?

=IF(SUBTOTAL(9,S3:S86)>0,SUBTOTAL(9,S3:S86),0)

Hi Fin Fang Foom:

Your formula ... =IF(SUBTOTAL(9,S3:S86)>0,SUBTOTAL(9,S3:S86),0)

worked for me. Am I missing something here?

 

[Fin Fang Foom]

Me too I dont know why it does not work. Look in cell U25 it should be 6.44 The formula should just sum positive Subtotals, Right?


What do you think is the problem?

 

[Yogi Anand]

Hi Fin Fang Foom:

I think the problem is that you are making the wrong comparison.

Book2
	U	V	W	X
3
4	-1.2
5	-0.18
6	-0.17
7	0.29
8	-1.26
9
10	-2.33
11	-2.33
12
13	0.23
14	1.69
15	4.01
16	5.93
17
18	0.5
19	0
20	0.5
21
22	-3.06
23	-3.06
24	-0.22
25	0
26	6.44	-0.22	-0.22
27
Sheet7

Formula in cell U25 ... =IF(SUBTOTAL(9,U3:U23)>0,SUBTOTAL(9,U3:U23),0)

is correvtly displaying as 0, since the total of the entries in range U3:U23

is to be obtained by either ...

=SUM(U8,U11,U16,U20,U23) as depicted in cell V26
or
=SUM(U3:U23)/2 as depicted in cell W26

I don't know the logic of the formula you used to arrive at the figure in cell U26.

 

[PaddyD]

for what it's worth, subtotal() doesn't like conditiona arrays, so if what you are trying to do cn be summarised as:

sum a range of cells, but only those that are both visible after a filter has been applied and greater than zero

...then you'll need a different construction. The filtering hasn't survived the html process, but in the original col a had been filtered to show 'b':

Book2
	A	B	C	D
1	ID	Num		8
2	a	1
3	b	-1
4	a	1
5	b	-1
6	a	1
7	b	3
8	a	1
9	b	5
Sheet2

Formula is:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(FilterRange,ROW(FilterRange)-MIN(ROW(FilterRange)),,1)),--(FilterRange>0),FilterRange)

...where FilterRange refers to B2:B9

 

[Fin Fang Foom]

The value is still off a little bit. I'm I doing something wrong?
X24 should be 6.44.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(U3:U23,ROW(U3:U23)-MIN(ROW(U3:U23)),,1)),--(U3:U23>0),U3:U23)

Any Ideas?

 

[Fin Fang Foom]

I still can't get it to work.

Any Help?

 

[Domenic]

Try the following, which will require the use of a helper column...

V4, copied down:

=IF(U5="",U4,"")

Then use the following formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(V4:V23,ROW(V4:V23)-MIN(ROW(V4:V23)),0,1)),(V4:V23>0)+0,V4:V23)

or

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(V4:V23,ROW(V4:V23)-MIN(ROW(V4:V23)),0,1))>0),V4:V23)

Hope this helps!

Edit: Adjusted the range.