|
Simplified SUMIF Needed
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? |
Simplified SUMIF Needed
Try this:
=SUMIF(CS1:CS24!I10:I119,"=1",CS1:CS24!F10:F119) Hope this helps. Pete On Aug 21, 1:19 pm, Funkydan wrote: 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? |
Simplified SUMIF Needed
On 21 Aug, 13:36, Pete_UK wrote:
Try this: =SUMIF(CS1:CS24!I10:I119,"=1",CS1:CS24!F10:F119) Hope this helps. Pete On Aug 21, 1:19 pm, Funkydan wrote: 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? I tried that Pete and i also tried =SUMIF('CS1:CS24'!I10:I33,1,'CS1:CS24'!F10:H33) and I Get #Value! |
Simplified SUMIF Needed
On Tue, 21 Aug 2007 05:36:03 -0700, Pete_UK wrote:
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 |
Simplified SUMIF Needed
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 ups.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? |
Simplified SUMIF Needed
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 |
Simplified SUMIF Needed
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 |
Simplified SUMIF Needed
Lori, Did you test this?
-- Don Guillett Microsoft MVP Excel SalesAid Software "Lori" wrote in message ... 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 ups.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? |
Simplified SUMIF Needed
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,IND IRECT(list&"!F10:F119"))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Funkydan" wrote in message ups.com... 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 |
Simplified SUMIF Needed
On 22 Aug, 01:56, "Ragdyer" wrote:
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,IND IRECT(list&"!F10:F119"))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Funkydan" wrote in message ups.com... 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 Thank You Very Much Managed To Do it :) |
Simplified SUMIF Needed
You're welcome, and appreciate the feed-back.
-- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Funkydan" wrote in message ps.com... On 22 Aug, 01:56, "Ragdyer" wrote: 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,IND IRECT(list&"!F10:F119"))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- [snip] Thank You Very Much Managed To Do it :) |
| All times are GMT +1. The time now is 07:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com