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]

Hey PA HS Teacher,

I'll try it and I will post back.

 

[Fin Fang Foom]

Hi PA HS Teacher,

How does your vba function works?

Because I'm not getting the correct value?

 

[PA HS Teacher]

Fin,

I) In what way is the solution not working. Do you get an error message, if so which one? Do you get an incorrect value? If so why is it incorrect.

II) Did you make sure to confirm the array formula,
=SUM(--NOT(hidden(A2:A20))*(A2:A20>0)*A2:A20)
with control + alt + enter

Otherwise:

1) Did you insert a module.
2) Copy the FUNCTION (not a sub) exactly as written?
3) test it on a single cell to make sure the Hidden() function works for you.
e.g. enter =Hidden(A2)
Should return False if A2 is visible and True if it is not.
4) Test it on a Range of Cells, e.g. =Hidden(A2:A10) confirmed with Control + Shift + Enter
Confirm it down across multiple cells.
You should see Trues Corresponding to the hidden cells, and false corresponding to the visible cells. (use autofilter to hide and unhide the cells in A2:A10)

In the example I have contrived, the function sums all values within a range that are Positive and Note Hidden.

If you have tried all of the above, could you post a small 5 line or so example, including a negative number that is not hidden and a row to be hidden.

Post the result you are expecting to get, and the result you are getting from my Array Entered Formula.

Paddy is right, You may want to reavaluate the way you set up your data.

 

[Fin Fang Foom]

The formula gave me 13.16. It should give me 6.44.

I did insert the module under the workbook as a Function.

What I'm doing wrong?

 

[PA HS Teacher]

.23 + 1.69 + 4.01 + 5.94 + .50 + .50 = 13.16

The formula is doing what I expected it to do. It summed all visible rows greater than 0.

How do you arrive at 6.44?
Am I missing something?

 

[Fin Fang Foom]

I only want a grand subtotal formula to subtotal U16 and U20 because those are Subtotal formulas and there are positive values.

Do you think that could be done?

 

[PA HS Teacher]

Try this UDF:

Function MyPositiveSubtotals(rng As Range) As Double
Dim IndVal As Range
Dim Total, CurrentSub As Double
Dim ArrResult() As String, 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
ArrElemt = 0
Total = 0
For Each IndVal In rng
ArrElemt = ArrElemt + 1
  If Len((IndVal.Offset(1, 0).Value)) = 0 And ArrResult(ArrElemt) = False Then
     If CurrentSub > 0 Then Total = Total + CurrentSub
     CurrentSub = 0
  Else
     If ArrResult(ArrElemt) = False Then CurrentSub = CurrentSub + IndVal.Value
     If ArrResult(ArrElemt) = False And Len((IndVal.Offset(0, 0).Value)) = 0 Then CurrentSub = 0
  End If
Next IndVal
MyPositiveSubtotals = Total
End Function

 

[Fin Fang Foom]

The function gives me a #NAME? error.

I'am I suppose to use this formula?

=SUM(--NOT(hidden(U4:U23))*(U4:U23>0)*U4:U23)

 

[PA HS Teacher]

Try:
=MyPositiveSubtotals(U4:U23)

 

[Fin Fang Foom]

It gives me a 0.00 value?