Make sure to copy formulas *exactly*. There is an apostophe missing before
the first CS in both formulas, you also need to remove the "s from row() as a
reference must be used.

You may find Don's formula easier to understand with E2="CS1" and E3="CS2"
to sum the values on the first two sheets and then extend this to more values.

"Funkydan" wrote:

On 21 Aug, 15:32, Lori wrote:
You can't use 3D refs with indirect either but a minor adjustment to the
formula should do the trick: replace ["'"&E2:E3] with ["'CS"&ROW(1:24)].

"Don Guillett" wrote:
From a posting by Bob Phillips where e2 is the first sheet name and e3 is
the last sheet name
=SUMPRODUCT(SUMIF(INDIRECT("'"&E2:E3&"'!i1:i19"),1 ,INDIRECT("'"&E2:E3&"'!f1:f19")))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Funkydan" wrote in message
oups.com...
Hi All,

If There away can simplify a Sumif Function?

I have the following formula

=SUMIF('CS1'!I10:I119,"=1",'CS1'!F10:F119)
+SUMIF('CS2'!I10:I119,"=1",'CS1'!F10:F119)
+SUMIF('CS3'!I10:I119,"=1",'CS1'!F10:F119)
+SUMIF('CS4'!I10:I119,"=1",'CS1'!F10:F119)
+SUMIF('CS5'!I10:I119,"=1",'CS1'!F10:F119)
+SUMIF('CS6'!I10:I119,"=1",'CS1'!F10:F119)
+SUMIF('CS7'!I10:I119,"=1",'CS1'!F10:F119)
+SUMIF('CS8'!I10:I119,"=1",'CS1'!F10:F119)
+SUMIF('CS9'!I10:I119,"=1",'CS1'!F10:F119)
+SUMIF('CS10'!I10:I119,"=1",'CS1'!F10:F119)
and So On up to Cost Sheet CS24 and Then theres 24 IF's

is there a way i can Make its work like this?

=SUMIF('CS1-CS24'!IAll,"=1",'CS1-CS24'!FAll)

All CS1- CS24 Sheets are Identical Apart from One Sheet

Can this be done?

Thank you all for your help,

I Must be Missing somthing

The formula im using now is as followings and it gives me a #REF Error
now.

=SUMPRODUCT(SUMIF(INDIRECT("CS"&ROW(INDIRECT("1:24 "))&"'!I10:I200"),
23,INDIRECT("'CS"&ROW(INDIRECT("1:24"))&"'!F10:F20 0")))

i also tried the following as well (This Wont even allow me to use it.

=SUMPRODUCT(SUMIF(INDIRECT("CS"&ROW("1:24"))&"'!I1 0:I200"),
23,INDIRECT("'CS"&ROW("1:24"))&"'!F10:F200")))


by the was im using version XL 2003