Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Pl see the attachment. Instead of entering formula for each sheet, is there a way to minimize the sumif with multiple sheet?
    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel has no built-in support for SUMIF or COUNTIF across multiple sheets.
    It is possible to create custom VBA functions that provide this capability - see the section POWER PROGRAMMING TECHNIQUES By Myrna Larson and David Hager in Excel Experts E-letter 3.

    However, it will be impossibe to use them in your example since the ranges on the two sheets are different (A8:B11 on Sheet1 and A1:B4 on Sheet2). You will have to ensure that the ranges are the same on all sheets.

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788048' date='08-Aug-2009 15:50']Excel has no built-in support for SUMIF or COUNTIF across multiple sheets.
    It is possible to create custom VBA functions that provide this capability - see the section POWER PROGRAMMING TECHNIQUES By Myrna Larson and David Hager in Excel Experts E-letter 3.

    However, it will be impossibe to use them in your example since the ranges on the two sheets are different (A8:B11 on Sheet1 and A1:B4 on Sheet2). You will have to ensure that the ranges are the same on all sheets.[/quote]
    Thanks Hans, but not able to use the function. I have Copied/paste both code (Parse3DRange &SumIf3D). Pl advise what to do Now.
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You MUST ensure that the ranges to be used in the SumIf3D formula are in the same location on each sheet, say A1:B4. You can then use formulas such as

    =SumIf3D("Sheet1:Sheet2!$A$1:$A$4",E1,$B$1:$B$4)

    where E1 contains the condition. See the attached version.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='prasad' post='788046' date='08-Aug-2009 10:04']Pl see the attachment. Instead of entering formula for each sheet, is there a way to minimize the sumif with multiple sheet?[/quote]

    Sheet1
    ___|____A____]____B____|____C_____|____D____|
    1__|__Pizza___]____ ____|_____ _____|____ ____|
    2__|__Bread___|____ ____|_____ _____|____ ____|
    3__|_HotDog___|____ ____|_____ _____|____ ____|
    4__|_Sandwich_]____ ____|_____ _____|____ ____|
    5__|____ _____]____ ____|_____ _____|____ ____|
    6__|____ _____]____ ____|_____ _____|____ ____|
    7__|____ _____]____ ____|_____ _____|____ ____|
    8__|___Pizza__]____2____|_____ _____|____ ____|
    9__|___Bread__]____4____|_____ _____|____ ____|
    10_|__HotDog__]____6____|_____ _____|____ ____|
    11_|__Sandwich_]____8____|_____ _____|____ ____|

    Sheet2
    ___|____A_____]____B____|____C_____|____D____|
    1__|___Pizza___]____1____|_____ _____|____ ____|
    2__|___Bread__]____2_____|_____ _____|____ ____|
    3__|__HotDog__]____3_____|_____ _____|____ ____|
    4__|__Sandwich_]____4____|_____ _____|____ ____|

    1] All datas as per above 2 tables.

    2] Sheet1, B1 entered formula and copied down :

    =SUM(SUMIF(INDIRECT({"Sheet1";"Sheet2"}&"!"&{"A8:A 11";"A1:A14"}),A1,INDIRECT({"Sheet1";"Sheet2"}&"!" &{"B8:B11";"B1:B14"})))

    Regards
    Bosco

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788052' date='08-Aug-2009 17:02']You MUST ensure that the ranges to be used in the SumIf3D formula are in the same location on each sheet, say A1:B4. You can then use formulas such as

    =SumIf3D("Sheet1:Sheet2!$A$1:$A$4",E1,$B$1:$B$4)

    where E1 contains the condition. See the attached version.[/quote]
    I have converted all the codes into Add-in. Working fine so far. Is it possible to insert " in formula by default instead of putting manually.
    Regards
    Prasad

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm not sure I understand your question, but if you want to omit the quotes around Sheet1:Sheet2!$A$1:$A$4 in the formula

    =SumIf3D("Sheet1:Sheet2!$A$1:$A$4",E1,$B$1:$B$4)

    the answer is no. The first argument must be a string.

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788245' date='10-Aug-2009 17:53']I'm not sure I understand your question, but if you want to omit the quotes around Sheet1:Sheet2!$A$1:$A$4 in the formula

    =SumIf3D("Sheet1:Sheet2!$A$1:$A$4",E1,$B$1:$B$4)

    the answer is no. The first argument must be a string.[/quote]
    No Hans, i dont want to remove the quotes. When I simply enter formula & select sheet2 using shift key, it looks like =SumIf3D(Sheet1:Sheet2!$A$1:$A$4,E1,$B$1:$B$4) and result appear as #value. I have to put quotes manually in range to get the results. What I want is to make the " " as default part of formula.
    Regards
    Prasad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As I mentioned already, the first argument must be a string. You will have to add the quotes manually, there is no way to have Excel do that automatically.

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788251' date='10-Aug-2009 18:12']As I mentioned already, the first argument must be a string. You will have to add the quotes manually, there is no way to have Excel do that automatically.[/quote]
    right Hans, Got it. Thanks so much.
    Regards
    Prasad

  11. #11
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='bosco_yip' post='788163' date='09-Aug-2009 22:44']Sheet1
    __|____A____]____B____|____C____|____D____|
    1__|__Pizza___]____ ____|_____ _____|____ ____|
    2__|__Bread___]____ ____|_____ _____|____ ____|
    3__|_HotDog__]____ ____|_____ _____|____ ____|
    4__|_Sandwich_]____ ____|_____ _____|____ ____|
    5__|____ ____]____ ____|_____ _____|____ ____|
    6__|____ ____]____ ____|_____ _____|____ ____|
    7__|____ ____]____ ____|_____ _____|____ ____|
    8__|___Pizza__]____2____|_____ _____|____ ____|
    9__|___Bread__]____4____|_____ _____|____ ____|
    10_|__HotDog_]____6____|_____ _____|____ ____|
    11_|__Sandwich_]____8____|_____ _____|____ ____|

    Sheet2
    __|____A____]____B____|____C____|____D____|
    1__|___Pizza__]____1____|_____ _____|____ ____|
    2__|___Bread__]____2____|_____ _____|____ ____|
    3__|__HotDog_]____3____|_____ _____|____ ____|
    4__|__Sandwich_]____4____|_____ _____|____ ____|

    1] All datas as per about 2 tables.

    2] Sheet1, B1 entered formula and copied down :

    =SUM(SUMIF(INDIRECT({"Sheet1";"Sheet2"}&"!"&{"A8:A 11";"A1:A14"}),A1,INDIRECT({"Sheet1";"Sheet2"}&"!" &{"B8:B11";"B1:B14"})))

    Regards
    Bosco[/quote]

    Hi Bosco

    I think this is simply great. I am assuming that this does work with more than 2 Sheets.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='franciz' post='788285' date='10-Aug-2009 18:03']I am assuming that this does work with more than 2 Sheets.[/quote]
    It does - just add more sheet names to the list. If you have many sheets, it is easier to enter the sheet names in a range of cells, say H1:H10 if you have 10 sheets. You can then use H1:H10 instead of {"Sheet1";"Sheet2"}

    (If the sheet names may contain spaces, you must use "'"&H1:H10&"'")

  13. #13
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788287' date='11-Aug-2009 00:11']It does - just add more sheet names to the list. If you have many sheets, it is easier to enter the sheet names in a range of cells, say H1:H10 if you have 10 sheets. You can then use H1:H10 instead of {"Sheet1";"Sheet2"}

    (If the sheet names may contain spaces, you must use "'"&H1:H10&"'")[/quote]

    Hi Hans

    Thanks for the tip.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •