Simplified SUMIF Needed

[Funkydan]

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?

 

[Pete_UK]

Try this:

=SUMIF(CS1:CS24!I10:I119,"=1",CS1:CS24!F10:F119)

Hope this helps.

Pete

 

[Funkydan]

Try this:

=SUMIF(CS1:CS24!I10:I119,"=1",CS1:CS24!F10:F119)

Hope this helps.

Pete

I tried that Pete and i also tried

=SUMIF('CS1:CS24'!I10:I33,1,'CS1:CS24'!F10:H33)

and I Get #Value!

 

[Ron Rosenfeld]

Try this:

=SUMIF(CS1:CS24!I10:I119,"=1",CS1:CS24!F10:F119)

Hope this helps.

Pete

At least in XL2003 SUMIF is not listed as a function that can be used in a 3D
reference, and your formula will return a VALUE error. I don't know about
2007, nor did the OP indicate which version of XL he is using.
--ron

 

[Don Guillett]

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")))

 

[Guest]

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)].

 

[Funkydan]

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)].

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
(e-mail address removed)

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:F200")))

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

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


by the was im using version XL 2003

 

[Guest]

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.

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)].

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
(e-mail address removed)
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?

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:F200")))

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

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


by the was im using version XL 2003

 

[Don Guillett]

Lori, Did you test this?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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)].

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
(e-mail address removed)

 

[Ragdyer]

The best way I have seen this done is by creating an actual list of the
sheets, and then ... refer to this data list of sheet names in the formula.

Since your sheets are in numerical order, you can easily create this list by
simply dragging down to copy.
In an out-of the-way location, say Z1, enter:
CS1
Then click the fill handle and drag down to Z24.
This gives you the list of sheet names.
Make *sure* your sheet tabs match this list *exactly*.

Then, try this formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z24&"'!I10:I119"),1,INDIRECT("'"&Z1:Z24&"'!F10:F119")))

BTW ... you can also assign a name to this range of sheet names, and use
that in the formula instead.

Select Z1 to Z24, and click in the name box, and type in an appropriate
short name, say,
list
Then hit <Enter>

You formula can now look like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!I10:I119"),1,INDIRECT("'"&list&"'!F10:F119")))

Although it *is* safer to include the apostrophes in the sheet names - "just
in case",
since your sheet names *do not* contain spaces, you could revise your
formula to this:

=SUMPRODUCT(SUMIF(INDIRECT(list&"!I10:I119"),1,INDIRECT(list&"!F10:F119")))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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)].

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
(e-mail address removed)
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?

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:F200")))

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

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


by the was im using version XL 2003

 

[Funkydan]

The best way I have seen this done is by creating an actual list of the
sheets, and then ... refer to this data list of sheet names in the formula.

Since your sheets are in numerical order, you can easily create this list by
simply dragging down to copy.
In an out-of the-way location, say Z1, enter:
CS1
Then click the fill handle and drag down to Z24.
This gives you the list of sheet names.
Make *sure* your sheet tabs match this list *exactly*.

Then, try this formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z24&"'!I10:I119"),1,INDIRECT("'"&Z1:Z24&"'!F10:F119")))

BTW ... you can also assign a name to this range of sheet names, and use
that in the formula instead.

Select Z1 to Z24, and click in the name box, and type in an appropriate
short name, say,
list
Then hit <Enter>

You formula can now look like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!I10:I119"),1,INDIRECT("'"&list&"'!F10:F119")))

Although it *is* safer to include the apostrophes in the sheet names - "just
in case",
since your sheet names *do not* contain spaces, you could revise your
formula to this:

=SUMPRODUCT(SUMIF(INDIRECT(list&"!I10:I119"),1,INDIRECT(list&"!F10:F119")))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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)].
:
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
(e-mail address removed)
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.

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

by the was im using version XL 2003

Thank You Very Much Managed To Do it

 

[RagDyeR]

You're welcome, and appreciate the feed-back.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

The best way I have seen this done is by creating an actual list of the
sheets, and then ... refer to this data list of sheet names in the
formula.

Since your sheets are in numerical order, you can easily create this list
by
simply dragging down to copy.
In an out-of the-way location, say Z1, enter:
CS1
Then click the fill handle and drag down to Z24.
This gives you the list of sheet names.
Make *sure* your sheet tabs match this list *exactly*.

Then, try this formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z24&"'!I10:I119"),1,INDIRECT("'"&Z1:Z24&"'!F10:F119")))

BTW ... you can also assign a name to this range of sheet names, and use
that in the formula instead.

Select Z1 to Z24, and click in the name box, and type in an appropriate
short name, say,
list
Then hit <Enter>

You formula can now look like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!I10:I119"),1,INDIRECT("'"&list&"'!F10:F119")))

Although it *is* safer to include the apostrophes in the sheet names -
"just
in case",
since your sheet names *do not* contain spaces, you could revise your
formula to this:

=SUMPRODUCT(SUMIF(INDIRECT(list&"!I10:I119"),1,INDIRECT(list&"!F10:F119")))

Thank You Very Much Managed To Do it