Simplified SUMIF Needed

F

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?
 
F

Funkydan

Try this:

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

Hope this helps.

Pete
Click to expand...

I tried that Pete and i also tried

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

and I Get #Value!
 
R

Ron Rosenfeld

Try this:

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

Hope this helps.

Pete
Click to expand...

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
 
D

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

Lori

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

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

Don Guillett said:
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")))
Click to expand...
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
Click to expand...

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
 
L

Lori

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 said:
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 said:
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")))
Click to expand...
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi All,
Click to expand...
If There away can simplify a Sumif Function?
Click to expand...
I have the following formula
Click to expand...
=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
Click to expand...
is there a way i can Make its work like this?

All CS1- CS24 Sheets are Identical Apart from One Sheet
Click to expand...
Can this be done?
Click to expand...
Click to expand...

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
Click to expand...
 
D

Don Guillett

Lori, Did you test this?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Lori said:
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 said:
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)
Click to expand...
Click to expand...
 
R

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 !
---------------------------------------------------------------------------

Funkydan said:
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 said:
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")))
Click to expand...
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi All,
Click to expand...
If There away can simplify a Sumif Function?
Click to expand...
I have the following formula
Click to expand...
=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
Click to expand...
is there a way i can Make its work like this?

All CS1- CS24 Sheets are Identical Apart from One Sheet
Click to expand...
Can this be done?
Click to expand...
Click to expand...

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
Click to expand...
 
F

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?
Click to expand...
Click to expand...
Thank you all for your help,
Click to expand...
I Must be Missing somthing
Click to expand...
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
Click to expand...
Click to expand...

Thank You Very Much Managed To Do it :)
 
R

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")))
Click to expand...

Thank You Very Much Managed To Do it :)
 
Top