Excel 2003 Adding Unwanted Brackets [ ] to Sumproduct Formula

**wyowok** · 11-11-2011, 01:04 PM

I am working in Excel 2003. I am working only within a single workbook.

I am using sumproduct across multiple worksheets (25) to a single summary worksheet.

The formula I am using is as follows:

=SUMPRODUCT(('1:25'!A3:A85=A2)*('1:25'!E3:E85=YES))

Worksheets are simply numbered 1 through 25.

However, when I hit Enter the following formula replaces mine seemingly indicating another workbook called "25" as designated by the addition of the brackets [ ]?

=SUMPRODUCT(('1:[25]25'!A3:A85=A2)*('1:[25]25'!E3:E85=YES))

Why is it doing this and how do I get it to just sum across worksheets 1-25 within the workbook?

**martindwilson** · 11-11-2011, 03:17 PM

you cant use sumproduct in a 3d way like that

**wyowok** · 11-11-2011, 04:45 PM

Originally Posted by martindwilson

you cant use sumproduct in a 3d way like that

What is a better way to achieve this without using sumproduct? Thanks.

**snb** · 11-12-2011, 07:10 AM

In every sheet you can enter the formula:

PHP Code:


=SUMPRODUCT((A3:A85='1'!A2)*(E3:E85="yes"))

If you do that e.g. in every sheet in cell H5

you can use the formula

PHP Code:


=SUM('1:25'!H5)

to get the total.

**martindwilson** · 11-12-2011, 07:26 AM

forgot that approach.as an addition you don't need to enter the formula 25 times
select all sheets(group) and enter the formula =SUMPRODUCT((A3:A85='1'!A2)*(E3:E85="yes")) in say b1,it will be repeated across all the sheets, then un group

**daddylonglegs** · 11-12-2011, 09:46 AM

Originally Posted by wyowok

The formula I am using is as follows:

=SUMPRODUCT(('1:25'!A3:A85=A2)*('1:25'!E3:E85=YES))

What's in A2? Assuming that's a text value then try using this "array formula" in the summary sheet

=SUM(IF(T(INDIRECT("'"&ROW(INDIRECT("1:25"))&"'!A"&TRANSPOSE(ROW(INDIRECT("3:85")))))=A2,T(INDIRECT("'"& ROW(INDIRECT("1:25"))&"'!E"&TRANSPOSE(ROW(INDIRECT("3:85")))))="Yes")+0)

confirmed with CTRL+SHIFT+ENTER

If A2 is numeric then change the red T to N

**wyowok** · 11-14-2011, 02:10 PM

I cut and pasted this formula and entered as an array: Control+Shift+Enter:

=SUM(IF(T(INDIRECT("'"&ROW(INDIRECT("1:25"))&"'!A"&TRANSPOSE(ROW(INDIRECT("3:85")))))=A2,T(INDIRECT("'"& ROW(INDIRECT("1:25"))&"'!E"&TRANSPOSE(ROW(INDIRECT("3:85")))))="Yes")+0)

I am getting an #REF error message?

I attached a Print Screen shot of my spreadsheet. Column A does contain text (peoples names).

**daddylonglegs** · 11-14-2011, 02:42 PM

#REF! probably indicates a problem with the sheet names, do you definitely have 1 to 25 (I can only see up to 21 in the screenshot)?

The names must be exactly those numbers. If one of the sheet names has a space or other characater within it then the formula won't work

Test by adding a blank sheet and put 1 to 25 in A1:A25 then in B1 this formula copied down

=INDIRECT("'"&A1&"'!A1")

That should give you the A1 value from each sheet. If any one or more gives you a #REF! error then the sheet name isn't exactly that number.....

**wyowok** · 11-14-2011, 07:45 PM

Originally Posted by daddylonglegs

#REF! probably indicates a problem with the sheet names, do you definitely have 1 to 25 (I can only see up to 21 in the screenshot)?

The names must be exactly those numbers. If one of the sheet names has a space or other characater within it then the formula won't work

Test by adding a blank sheet and put 1 to 25 in A1:A25 then in B1 this formula copied down

=INDIRECT("'"&A1&"'!A1")

That should give you the A1 value from each sheet. If any one or more gives you a #REF! error then the sheet name isn't exactly that number.....

It worked!! I found that worksheets 2 and 3 had a space after the number I couldn't see. Once I deleted the space it worked like a charm.

Thanks so much for your help!!