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

 

[jasonb75]

yours is working without the condition i want.can u add the condition in the vba.thanks

How about

Option Explicit
Public Function Sum_All(rng As Range, exclude As String, Optional ignore As Long = 50) As Double
Application.Volatile
Dim ws As Object, Sum_Temp As Double
For Each ws In Worksheets
    If ws.Name <> exclude Then
        If ws.Range(rng.Address).Value >= ignore Then Sum_Temp = Sum_Temp + ws.Range(rng.Address).Value
    End If
Next
Sum_All = Sum_Temp
End Function

To use the default condition of 50

=Sum_All(A1,"Sheet1")

or to use a different condition, for example 100

=Sum_All(A1,"Sheet1",100)

 

[taurean]

Sunday is leisure day. But Monday hits you hard. Sorry for replying late. The VBA code did not error out (but then I do not have 1000 sheets). That is really a big number. Putting in lot of formulae may give you a performance hit like slow workbook. You can reconsider your data arrangement. Little out of the way but if the data corrupts (I do not know if that starts occurring at such huge numbers) then it will give you headache whole day.

Here is one way. Put the code below in ThisWorkBook Module. This is worksheet change based event.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSum As Double
Application.EnableEvents = False
'The event will trigger only in case of Sheet1 and cell A2 only
If Sh.Name = "Sheet1" And Target.Address = "$A$2" Then
sSum = 0
    For i = 1 To Sheets.Count
        If Sheets(i).Range("A1").Value2 >= 50 Then
        sSum = sSum + Sheets(i).Range("A1").Value2
        End If
    Next i
Sheet1.Range("A2").Value2 = sSum
End If
Application.EnableEvents = True
End Sub

Now all you need to do is go to Sheet1 and cell A2. Change something in the cell and then hit enter. VBA will put in the total number.

The code can be put into following WorkSheet level code but then you will have to keep shifting the code if you plan to change the sheet.

 

[taurean]

If you have enough space on first sheet then you can implement following formula and if the names are Sheet1, Sheet2 (something sequential).

In Column A put in serial numbers and then formula in B column as specified below then all data will reflect on Sheet1.

To which you can apply regular SUMIF.

Excel Workbook
	A	B
1	50
2	1	50
3	2	100
4	3	30
5		150
Sheet1

 

[spilner]

How about

Option Explicit
Public Function Sum_All(rng As Range, exclude As String, Optional ignore As Long = 50) As Double
Application.Volatile
Dim ws As Object, Sum_Temp As Double
For Each ws In Worksheets
    If ws.Name <> exclude Then
        If ws.Range(rng.Address).Value >= ignore Then Sum_Temp = Sum_Temp + ws.Range(rng.Address).Value
    End If
Next
Sum_All = Sum_Temp
End Function

To use the default condition of 50

=Sum_All(A1,"Sheet1")

or to use a different condition, for example 100

=Sum_All(A1,"Sheet1",100)

jason,yours is working but how i add range of sheets?

example i want

=Sum_All(A1,"1:100")

it not work.can u fix it?

 

[taurean]

Did you try the solutions I have posted at #32 and #33?

 

[spilner]

i try the #32 VBA

i reenter in A2 and nuthin come out.

the #33.not sure if thats what i want.
i have thousands of sheets.do i need to list all the sheets in A cell?

 

[taurean]

#32:
Check #1: Have you pasted the code in correct module?
i.e. ThisWorkbook which appears only in VBE window.
Check #2 : Sheet Name is Sheet1

#33:
Will work if only all names are logical:
Sheet1, Sheet2.................Sheetn like that then
In Column A insert numbers serially as shown till the nth sheet.

in cell B2:
=INDIRECT("Sheet"&A2&"!A1")
And then copy down the formula

Excel will autofill it as per references.

And then in B (n+1) cell enter regular SUMIF.

 

[spilner]

the #32 i paste the code at the ThisWorkbook.i did F2 in A2 and enter still nuthing come out.

 

[spilner]

i restart excel and try #32 it worked.thanks man

 

[spilner]

#32:
Check #1: Have you pasted the code in correct module?
i.e. ThisWorkbook which appears only in VBE window.
Check #2 : Sheet Name is Sheet1

#33:
Will work if only all names are logical:
Sheet1, Sheet2.................Sheetn like that then
In Column A insert numbers serially as shown till the nth sheet.

in cell B2:
=INDIRECT("Sheet"&A2&"!A1")
And then copy down the formula

Excel will autofill it as per references.

And then in B (n+1) cell enter regular SUMIF.

how do i sum range of sheets using the #32?

where do i need to change in the code?

from 1:1000