Subtotal formula Subtotals greater than zero needed.

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Jul 5, 2005

• #2

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

 

Upvote 0

Fin Fang Foom

Well-known Member

Joined

Mar 20, 2005

Messages

598

• Jul 5, 2005

• #3

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

Is this possible?

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Jul 5, 2005

• #4

Fin Fang Foom said:

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)

Click to expand...

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?

 

Upvote 0

Fin Fang Foom

Well-known Member

Joined

Mar 20, 2005

Messages

598

• Jul 5, 2005

• #5

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?

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Jul 5, 2005

• #6

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.

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Jul 5, 2005

• #7

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

 

Upvote 0

Fin Fang Foom

Well-known Member

Joined

Mar 20, 2005

Messages

598

• Jul 5, 2005

• #8

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?

 

Upvote 0

Fin Fang Foom

Well-known Member

Joined

Mar 20, 2005

Messages

598

• Jul 6, 2005

• #9

I still can't get it to work.

Any Help?

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,578

Office Version

1. 365

Platform

1. Windows

• Jul 6, 2005

• #10

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.

 

Upvote 0