sum cell all sheets with condition

spilner

Board Regular
Joined
Jun 7, 2011
Messages
146
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
 
@marcelo and aladin

i got it worked but it not work if i have 1000 sheets.

@taurean

it work perfectly man.

thanks to all that help much in this.you guys is the best :) good cooperation
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
hi,ive thousands of sheets.

im trying to run this vba but it end with error.i saved the file as macro enabled or theres any other way to save macro file?

Code:

Public Sub BuildMyFormula()
Dim sFormula As String
'Provide the cell address in format like A1, B1 etc
CellName = InputBox("Please provide cell address like A1", "Cell Address")
sFormula = ""
'This builds up the formula on its own
For i = 1 To Sheets.Count
sFormula = sFormula & "(" & Sheets(i).Name & "!" & CellName & ">=50)" & "*" & _
Sheets(i).Name & "!" & CellName & ","
Next i
'Removes unnecessary comma
sFormula = Left(sFormula, Len(sFormula) - 1)
'Sets the formula in active cell
ActiveCell.Formula = "=SUM(" & sFormula & ")"
End Sub



when i click debug,it highlighted yellow this line

ActiveCell.Formula = "=SUM(" & sFormula & ")"

can u help tauren?
 
Upvote 0
yes i really need them.i use it for my work.can u help fix the line aladin?
i owe u so much.thanks
 
Upvote 0
Taureans suggestion is creating the formula for you which would work with a small selection, but would far exceed the formula length limit if you literally have "thousands" of sheets.

The second most practical way I can think of would be something on the lines of

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

Copy the code, then use the formula below to get the result from it.

=Sum_All(A1,"Sheet1")

Where A1 is the cell to sum from each sheet and "Sheet1" is the name of the sheet where the total is going, which I assume should be excluded from the sum.
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,292
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top