sum cell all sheets with condition

[spilner]

okay,i want to sum all sheets with a condition.

example :
its all in the same cell example A1

sheet 1 = 50 , sheet 2 = 100 , sheet 3 = 1000 , sheet 4 = 30

if its more than 50 sum it.if its below than 50 then 0.

my formula is like this but it aint work.im not very good in excel.

=SUMIF('1:4'!A11,min(50,a11),0)

i dont know how to write this,hope someone can help me.thanks a lot guys

 

[spilner]

will wait your new code..thanks a lot

 

[jasonb75]

Ok, think this is right,

Option Explicit
Public Function Sum_All(rng As Range, start As String, finish As String, Optional ignore As Long = 50) As Double
Application.Volatile
Dim Sum_Temp As Double, ws As Long, sIndex As Long, eIndex As Long
sIndex = Sheets(start).Index
eIndex = Sheets(finish).Index
For ws = sIndex To eIndex
    If Sheets(ws).Range(rng.Address) >= ignore Then
        Sum_Temp = Sum_Temp + Sheets(ws).Range(rng.Address)
    End If
Next
Sum_All = Sum_Temp
End Function

=Sum_All(A1,11,5) should give the correct result now.

 

[spilner]

now it work perfect.ill test running for few days and hope theres no problem.

thanks a lot jason.