SUMIF

[Guest]

I’m using Excel 2003 SP2, and I have a spreadsheet which shows the monthly
management accounts for a series of cost-centres, one per worksheet; the
worksheets are named “Sheet1†through to “Sheet20. Each worksheet has five
columns and about a hundred rows, and their layout is identical.

I also have a worksheet that produces an account for the combined cost
centres, using =SUM(Sheet1:Sheet20!E6) etc. for each cell. So far so good.

The problem is that the cost-centres are divided into sub-groups, and I want
to produce sub-totals for each sub-group. Cell B2 on each worksheet contains
the reference for the sub-group that the particular cost centre belongs to,
ranging from 1 through to 8.

I’ve used the following formula for sub-group 1, but it produces a #NAME?
error:

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheet1:sheet20&"'b2"),1,INDIRECT("'"&sheet1:sheet20&"'!e6")))

I guess the fact that Excel isn’t capitalising the initial letter of the
worksheet name is a clue, but can anyone tell me what I’m doing wrong?

 

[Roger Govier]

Hi Ian

From a previous posting by Biff (T Valko)

If you create a list of your sheet names and call it something like
Snames then the following formula will work

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!b2"),1,INDIRECT("'"&Snames&"'!b6")))

 

[Guest]

Roger

It works! Many thanks for your help.

Ian

 

[Roger Govier]

Hi Ian

You're very welcome. Thanks for the feedback, but the thanks are really
due to Biff who posted the original solution.