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]

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

Try...

A1: 1
A2: 2
A3: 3
A4: 4

Select A1:A4, go to the Name Box on the Formula bar, type SheetList, and enter.

Let B1 house 50, the criterion value.

Now invoke in say C1...

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A11"),">"&B1))

 

[Michael M]

could you also not use

=IF(SUM(Sheet1:Sheet4!A1)>50,SUM(Sheet1:Sheet4!A1),0)

 

[spilner]

maybe i get yall confuse.

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.

so,the total is 50+100+1000 = 1150.
sheet4 is less than 50.so,its not counted.

@michael
yours formula count all of in A1.

@aladin
it not work with me

can u guys write again?

 

[Aladin Akyurek]

maybe i get yall confuse.

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.

so,the total is 50+100+1000 = 1150.
sheet4 is less than 50.so,its not counted.

@michael
yours formula count all of in A1.

@aladin
it not work with me

can u guys write again?

Did you create the range in which the names of the relevant sheets must be entered and named that range SheetList?

 

[spilner]

it end with #name?

im not sure why its involed with b1.
its all only in the same cell which its in A1 only.

 

[Aladin Akyurek]

it end with #name?

im not sure why its involed with b1.
its all only in the same cell which its in A1 only.

You have sheets name 1, 2, 3, and 4. Right?

Let's enter in A1:A4 these sheet names say on the Summary sheet.
Select A1:A4 on the Summary sheet, name this range SheetList via the Name Box on the Formula Bar.

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

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

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A11"),">"&B1))

 

[spilner]

yes but i dont understand a1:a4.

i mean is like this..

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

i try to do what u say but i cant get it right.

 

[Marcelo Branco]

You have sheets name 1, 2, 3, and 4. Right?

Let's enter in A1:A4 these sheet names say on the Summary sheet.
Select A1:A4 on the Summary sheet, name this range SheetList via the Name Box on the Formula Bar.

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

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

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A11"),">"&B1))

Spilner and Aladin,

Let me get in and trying to help.

I think the sheet-names are
Sheet1
Sheet2
Sheet3
Sheet4

and the interest cell is A1 (not A11)

M.

 

[spilner]

ur correct marcelo

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

Total : 1150

hope aladin can fix it.thanks