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)

 

[Fin Fang Foom]

How come the way you told me works? Why not put in the same collumn where original subtotals are? Is this what you calls a ghost in excel?
How can we have the value U25 to be 6.44?

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(U4:U23,ROW(U4:U23)-MIN(ROW(U4:U23)),0,1))>0),U4:U23)
Is there a formula that I could put in collumn U instead of using a helper collumn? Does such a formula exist?
Does not matter how long the formula should be as long I could have such a formula.

 

[Fin Fang Foom]

I just dont understand. That I use these formulas and both are excatly the same but both of them gives me different values. Why is that?

Cell
U25:
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(U4:U23,ROW(U4:U23)-MIN(ROW(U4:U23)),0,1))>0),U4:U23)

Cell
Now V25
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(U4:U23,ROW(U4:U23)-MIN(ROW(U4:U23)),0,1))>0),U4:U23)


The answer in U25 should be 6.44

Please help

 

[Domenic]

That's because the SUBTOTAL function not only ignores hidden rows, it also ignores other subtotals within the range. The subtotals in Column U are ignored, while the values in Column V are not.

You could use the following formula...

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(U4,{0,6,9,14,18},0,{4,1,3,2,1}))>0),SUBTOTAL(9,OFFSET(U4,{0,6,9,14,18},0,{4,1,3,2,1})))

...however, it won't allow you to insert additional rows within the range. If you do, you would have to make the necessary adjustments to the array constants {...}. So I don't think that this is the solution you're looking for, is it?

 

[Fin Fang Foom]

That's because the SUBTOTAL function not only ignores hidden rows, it also ignores other subtotals within the range. The subtotals in Column U are ignored, while the values in Column V are not.

How come it ignores other Subtotals formulas? Thats weird.

The solution is not what I'm looking for because the marker I posted is just a sample it actualy a larger file and I do this daily so the next day it could be larger or smaller. If we could get this formula to work then all I have to do is to incorparate the formula into a VBA code and it will automatically put it there for me. So it does not matter how big the formula might be.

Is there a formula for this do you think we need some kind of round function or frequency formula?

 

[Domenic]

How come it ignores other Subtotals formulas?

That's by design, in order to avoid double counting.

Is there a formula for this do you think we need some kind of round function or frequency formula?

Unfortunately, I don't have a solution to offer. Hopefully, someone else will be able to help.

 

[Fin Fang Foom]

Is there anyone could help on this topic?

 

[PA HS Teacher]

Can you spare another column?

If you want to subotal values in column U that are greater than 0.
Make colum V = U*(U>0)

Perform the subtotal on column V.

(I don't have a quick, flexible single cell solution either.)

 

[Fin Fang Foom]

Thanks for the reply PA HS Teacher,

I'm guessing that there is no such thing for this subtotal formula. So nobody ever thought about subtotaling greater than zeros.

If there is anybody and I mean anybody could help?

Please give me anything.

 

[PA HS Teacher]

=SUM(--NOT(hidden(Range))*(Range>0)*Range)
confirmed with control + shift + enter

Will work if you use the UDF I just wrote the returns true if a cell is hidden and false if it is not.

Function Hidden(rng As Range) As Variant
Dim ArrResult() As String, IndVal As Range, ArrElemt As Integer

'Dimension the array for the # elements you need
ReDim ArrResult(1 To rng.Cells.Count)

'Loop through each cell in the range
For Each IndVal In rng
    ArrElemt = ArrElemt + 1 'counter for the array
    If IndVal.Rows.Height = 0 Or IndVal.Columns.Width = 0 Then
       ArrResult(ArrElemt) = True
    Else
       ArrResult(ArrElemt) = False
    End If
Next IndVal

'Return the array results
Hidden = Application.WorksheetFunction.Transpose(ArrResult)
End Function

 

[PaddyD]

"So nobody ever thought about subtotaling greater than zeros."

Without wanting to sound too flipant, I would suggest that previously, when attempting to sum only subtotals that were greater than zero, from within a range of cells containing blanks, subtotal formulas and data, people adopted a data design more suited to the task.