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

 

[taurean]

1. Is the Sheet Name "Sheet1"?

2. Goto the Sub in VBE and add a break clicking on the left pane at the beginning of code.

Idea is when the event will be triggered you will be able to step through it .

 

[jasonb75]

Are your sheets literally named "1", "2", "3", or are they "Sheet1", "Sheet2", "Sheet3", or something else?

Need to identify exactly which sheets 1:100 refers to.

 

[spilner]

yes sheet1

can u paste the code for range 1:1000

next time ill change it myself.just want to know where to change it in the code.thanks

 

[taurean]

Unfortunately there will be no formula in #32 case.
The Sheets.Count in for loop will take care of all sheets. Be it 10 or 1000. It will go though all.

 

[spilner]

@jason
just the numbers 1 2 3 4 till 1000

 

[spilner]

Unfortunately there will be no formula in #32 case.
The Sheets.Count in for loop will take care of all sheets. Be it 10 or 1000. It will go though all.

thanks for your help tho.appreciate you still trying to help in this.hope jason can help.

 

[jasonb75]

Try

Option Explicit
Public Function Sum_All(rng As Range, start As Long, finish As Long, Optional ignore As Long = 50) As Double
Application.Volatile
Dim Sum_Temp As Double, ws As Long
On Error Resume Next
For ws = start To finish
    If Sheets("Sheet" & ws).Range(rng.Address).Value >= ignore Then
        Sum_Temp = Sum_Temp + Sheets("Sheet" & ws).Range(rng.Address).Value
    End If
Next
On Error GoTo 0
Sum_All = Sum_Temp
End Function

The formula now has 3 mandatory parameters and one optional.

To sum A1 in each sheet from "Sheet1" to "Sheet100" enter

=Sum_All(A1,1,100)

As before this would sum all values > = 50, to sum the same range for values > = 100 use

=Sum_All(A1,1,100,100)

Hopefully that will get you sorted.

 

[spilner]

it not work with me. this is example of my sheets. 11,1,3,4,8,5

i put in sheet11= A1=60 and sheet3= A1=60

so,i did as below from sheet11 to sheet5 the result turn 0.

=Sum_All(A1,11,5)

is there anything wrong i did?

 

[jasonb75]

Not you, code is written assuming that sheets are 1,2,3,4,5,...

I misread one of your earlier replies as well, I'll do a quick edit and post some new code now I know exactly what is needed.

 

[spilner]

cool...the sheets is unsorted...sorry to not let u know..