sumif with multiple sheets (EXcel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Thanks Hans, but not able to use the function. I have Copied/paste both code (Parse3DRange &SumIf3D). Pl advise what to do Now.

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

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.

Reply | Quote

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.

right Hans, Got it. Thanks so much.

Reply | Quote

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

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:A11″;”A1:A14”}),A1,INDIRECT({“Sheet1″;”Sheet2″}&”!”&{“B8:B11″;”B1:B14”})))

Regards
Bosco

Reply | 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 about 2 tables.

2] Sheet1, B1 entered formula and copied down :

=SUM(SUMIF(INDIRECT({“Sheet1″;”Sheet2″}&”!”&{“A8:A11″;”A1:A14”}),A1,INDIRECT({“Sheet1″;”Sheet2″}&”!”&{“B8:B11″;”B1:B14”})))

Regards
Bosco

Hi Bosco

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

Reply | Quote

I am assuming that this does work with more than 2 Sheets.

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&”‘”)

Reply | 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&”‘”)

Hi Hans

Thanks for the tip.

Reply | Quote