# sumproduct with multiple tab ranges

C

#### Chuck

hi all,

just curious if the following can be possible with SUMPRODUCT

i have designed a budget tracking sheet which calculates PO values &
account codes. right now, the sheet is using a pivot table to look
through all po tabs and provide a summary of account codes &
associated costs. unfortunately i have to always udpate the pivot
table once i modify a value in one of the PO tabs.

i am hoping to remove this pivot table and maybe use sumproduct (or
whatever that will work) to remove this dependancy.. so, i have a
standard sumproduct calculation as seen below

\$D\$33:\$D\$46 = account code
C5 = what the account code should be
K\$33:\$K\$46 = cost of the item (which should have that account code
assigned to it)

=SUMPRODUCT(--('001'!\$D\$33:\$D\$46=C5),--('002'!\$D\$33:\$D\$46=C5),--('003'!
\$D\$33:\$D\$46=C5),--('004'!\$D\$33:\$D\$46=C5),--('005'!\$D\$33:\$D\$46=C5),--
('006'!\$D\$33:\$D\$46=C5),--('007'!\$D\$33:\$D\$46=C5),--('008'!\$D\$33:\$D
\$46=C5),--('009'!\$D\$33:\$D\$46=C5),--('010'!\$D\$33:\$D\$46=C5),--('011'!\$D
\$33:\$D\$46=C5),--('012'!\$D\$33:\$D\$46=C5),--('013'!\$D\$33:\$D\$46=C5)*('001'!
\$K\$33:\$K\$46),--('002'!\$K\$33:\$K\$46),--('003'!\$K\$33:\$K\$46),--('004'!\$K
\$33:\$K\$46),--('005'!\$K\$33:\$K\$46),--('006'!\$K\$33:\$K\$46),--('007'!\$K
\$33:\$K\$46),--('008'!\$K\$33:\$K\$46),--('009'!\$K\$33:\$K\$46),--('010'!\$K
\$33:\$K\$46),--('012'!\$K\$33:\$K\$46),--('013'!\$K\$33:\$K\$46))

however, everytime i use the above, i get #VALUE eror.

can anyone advise if the above will work?

C

#### Chuck

i forgot to add that in most occassions

\$D\$33:\$D\$46 some of the cells maybe empty
K\$33:\$K\$46 some of the cells maybe empty

cheers

T

#### T. Valko

Try this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!D3346"),C5,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!K33:K46")))

P

#### Peo Sjoblom

One way

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"001";"002";"003";"004";"005";"006";"007";"008";"009";"010";"011";"012";"013"}&"'!\$D\$33:\$D\$46"),C5,INDIRECT("'"&{"001";"002";"003";"004";"005";"006";"007";"008";"009";"010";"011";"012";"013"}&"'!\$K\$33:\$K\$46")))

if you put the lists of all the sheet names in index order somewhere on the
sheet you are using for summary and call that range MySheets you can use a
shorter form

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!\$D\$33:\$D\$46"),C5,INDIRECT("'"&MySheets&"'!\$K\$33:\$K\$46")))

it's also easier to use if you want to add sheets without editing the
formula

--

Regards,

Peo Sjoblom

D

#### Dave Peterson

hi all,

just curious if the following can be possible with SUMPRODUCT

i have designed a budget tracking sheet which calculates PO values &
account codes. right now, the sheet is using a pivot table to look
through all po tabs and provide a summary of account codes &
associated costs. unfortunately i have to always udpate the pivot
table once i modify a value in one of the PO tabs.

i am hoping to remove this pivot table and maybe use sumproduct (or
whatever that will work) to remove this dependancy.. so, i have a
standard sumproduct calculation as seen below

\$D\$33:\$D\$46 = account code
C5 = what the account code should be
K\$33:\$K\$46 = cost of the item (which should have that account code
assigned to it)

=SUMPRODUCT(--('001'!\$D\$33:\$D\$46=C5),--('002'!\$D\$33:\$D\$46=C5),--('003'!
\$D\$33:\$D\$46=C5),--('004'!\$D\$33:\$D\$46=C5),--('005'!\$D\$33:\$D\$46=C5),--
('006'!\$D\$33:\$D\$46=C5),--('007'!\$D\$33:\$D\$46=C5),--('008'!\$D\$33:\$D
\$46=C5),--('009'!\$D\$33:\$D\$46=C5),--('010'!\$D\$33:\$D\$46=C5),--('011'!\$D
\$33:\$D\$46=C5),--('012'!\$D\$33:\$D\$46=C5),--('013'!\$D\$33:\$D\$46=C5)*('001'!
\$K\$33:\$K\$46),--('002'!\$K\$33:\$K\$46),--('003'!\$K\$33:\$K\$46),--('004'!\$K
\$33:\$K\$46),--('005'!\$K\$33:\$K\$46),--('006'!\$K\$33:\$K\$46),--('007'!\$K
\$33:\$K\$46),--('008'!\$K\$33:\$K\$46),--('009'!\$K\$33:\$K\$46),--('010'!\$K
\$33:\$K\$46),--('012'!\$K\$33:\$K\$46),--('013'!\$K\$33:\$K\$46))

however, everytime i use the above, i get #VALUE eror.

can anyone advise if the above will work?

C

#### Chuck

hi peo,

thanks for this.. i did try and apply

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"001";"002";"003";"004";"005";"006";"007";"­
008";"009";"010";"011";"012";"013"}&"'!\$D\$33:\$D
\$46"),C5,INDIRECT("'"&{"001";"002";"003";"004";"005";"006";"007";"008";"009";"010";"011";"012";"013"}&"­'!
\$K\$33:\$K\$46")))

but when i do, i get a #REF error .. saying invalid cell reference. i
know that \$D\$33:\$D\$46 & \$K\$33:\$K\$46 are the correct ranges for each
sheet. can it be due to a cell formatting issue?

C

#### Chuck

hi biff,

i also tried yours

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!
D3346­"),C5,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!
K33:K46")))

but getting the same error .. #REF .. invalid cell reference

C

#### Chuck

hi again biff,

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!D3346­"),C5,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!K33:K46")))

curious, what is the "000" for? the tab numbers?

C

#### Chuck

i found the issue with the #REF error. seems everytime i try and paste
the code in to excel, excel has a habit of putting a "-" somewhere and

cheers
chuck

T

#### T. Valko

Google Groups uses the "-" as line break character. You have to be careful

T

#### T. Valko

what is the "000" for? the tab numbers?

Yes.

ROW(INDIRECT("1:13"))

Returns an array as the numbers: 1, 2, 3...13

Putting that inside the TEXT function we can define a number format that
matches the name format of your sheets:

TEXT(ROW(INDIRECT("1:13")),"000")

Now that array looks like this: 001, 002, 003, ...013.

And the outer INDIRECT function finishes building the whole reference:

INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!D3346­")

This becomes:

'001'!D3346
'002'!D3346
'003'!D3346
.....
'013'!D3346

--
Biff
Microsoft Excel MVP

hi again biff,

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!D3346­"),C5,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!K33:K46")))

curious, what is the "000" for? the tab numbers?