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

 

[Aladin Akyurek]

ur correct marcelo

Sheet1 , A1=50
Sheet2 , A1=100
Sheet3 , A1=1000
Sheet4 , A1=30

Total : 1150

hope aladin can fix it.thanks

You have sheets name Sheet1, Sheet2, Sheet3, and Sheet4.
Enter in A1:A4 these sheet names say on a Summary sheet.
Select A1:A4 on the Summary sheet, name this range SheetList via the Name Box on the Formula Bar.

In E1 on the Summary sheet enter 50, your criterion value.

In F1 on the Summary sheet, enter the following formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A1"),">"&E1))

 

[Marcelo Branco]

You have sheets name Sheet1, Sheet2, Sheet3, and Sheet4.
Enter in A1:A4 these sheet names say on a Summary sheet.
Select A1:A4 on the Summary sheet, name this range SheetList via the Name Box on the Formula Bar.

In E1 on the Summary sheet enter 50, your criterion value.

In F1 on the Summary sheet, enter the following formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A1"),">"&E1))

Aladin,

I think it should be ">="&E1

See the expected result = 1150

M.

 

[taurean]

This will work but is extremely tedious to enter as the number of sheets will grow:

=SUM((Sheet1!A1>=50)*Sheet1!A1,(Sheet2!A1>=50)*Sheet2!A1,(Sheet3!A1>=50)*Sheet3!A1,(Sheet4!A1>=50)*Sheet4!A1)

 

[spilner]

it work taurean but i have thousands of sheets to count.got solution?

 

[Marcelo Branco]

it work taurean but i have thousands of sheets to count.got solution?

Spilner,

Please, follow Aladin's instructions. His formula is perfect.

M.

 

[spilner]

@aladin

i dont understand in this part.sorry aladin.

Enter in A1:A4 these sheet names say on a Summary sheet.
Select A1:A4 on the Summary sheet, name this range SheetList via the Name Box on the Formula Bar.

 

[Aladin Akyurek]

@aladin

i dont understand in this part.sorry aladin.

Enter in A1:A4 these sheet names say on a Summary sheet.
Select A1:A4 on the Summary sheet, name this range SheetList via the Name Box on the Formula Bar.

Which sheet do you want to have the formula?

 

[spilner]

anywhere in the sheet1 sir.

 

[Marcelo Branco]

@aladin

i dont understand in this part.sorry aladin.

Enter in A1:A4 these sheet names say on a Summary sheet.
Select A1:A4 on the Summary sheet, name this range SheetList via the Name Box on the Formula Bar.

I'll try to repeat Aladin's instructions, step by step

1.Create a new sheet and name it Summary

In this new sheet
2.type respectively in A1, A2, A3, A4
Sheet1
Sheet2
Sheet3
Sheet4

Go to Name box (beside the Formula Bar) and type in
SheetList
hit ENTER

3. Still in the Summary sheet
in E1 type 50
in F1 enter this formula
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A1"),">="&E1))

done!

M.

 

[taurean]

To implement the formula above maybe we can use VBA. Paste this VBA code in standard VBA module.
Step 1) Select cell where you need formula
Step 2) Run this code which will ask you for cell address (A1 in above case). Provide it.
Step 3) the formula will be inserted in the selected cell.

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