sumif with multiple sheets (EXcel 2003)

[prasad]

Pl see the attachment. Instead of entering formula for each sheet, is there a way to minimize the sumif with multiple sheet?

[HansV]

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.

[prasad]

[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.

[HansV]

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.

[bosco_yip]

[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

[prasad]

[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.

[HansV]

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.

[prasad]

[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.

[HansV]

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.

[prasad]

[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.

[franciz]

[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.

[HansV]

[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&"'")

[franciz]

[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.