Subtotal with IF formula

[ryan7]

My formula is

=SUBTOTAL(9,T4:T3551)

I want to add another criteria so that it only calculates cells with a value greater than Zero,

Can someone please help?

Thanks

 

[fadee2]

hi,
How about using

SUMIFS

instead?

 

[ryan7]

hi,
How about using

SUMIFS

instead?

Not sure how to use that.

 

[fadee2]

=sumifs(sum_range,criteria_range1,criteria,....)

Book1
	A	B
1	a	1
2	a	2
3	a	3
4	a	0
5	a	-1
6	b	5
7	b	1
8	b	6
9	b	0
10	a	1
11		7
Sheet2

Cell Formulas
Range	Formula
B11	B11	=SUMIFS(B1:B10,A1:A10,"a",B1:B10,">"&0)

like this

 

[Fluff]

How about

=SUMPRODUCT(SUBTOTAL(109,OFFSET(T$4,ROW(T$4:T$3551)-ROW(T$4),0)),--($T$4:$T$3551>0))

 

[ryan7]

How about

=SUMPRODUCT(SUBTOTAL(109,OFFSET(T$4,ROW(T$4:T$3551)-ROW(T$4),0)),--($T$4:$T$3551>0))

This Worked, Thank you.
Seems overly complicated to me but worked a treat.
Thanks Again

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ryan7]

Hi,
If I wanted to also include a SUM that holds the total when filtering cells, what would this be?

Thanks

 

[Fluff]

That formula does return the sum.

 

[ryan7]

That formula does return the sum.

It returns a subtotal. So when i apply a filter to look at specific rows the figure changes.
I'd also like to add a sum that retains the value.

Many thanks.