I have a number of Workbooks, each containing a single Worksheet.

Each WS contains similar data, i.e. uses the same column headings, but
is of variable length.

Column C in each WS is 'Location'(L1 - Lx).

Column J in each WS is 'Number of Widgets' (1 - N).

An L can appear several times in a WS, but not all Ls necessarily
appear in all WSs.

Every appearance of a L in a WS has a corresponding NoWs.

I want to be able to sum the NoWs for each L in each WS, and get a
single total for number of NoWs for each L in a WS.

Thus far I have used

'=SUMIF(C$2:C$60,C2,J$2;J$60)'

and dragged this into each row in the total column. This gives me the
correct answers, but repeats the answer for each L on every row that
that L appears in.

How can I modify this, or start again, such that I get a single total
for each L that appears in a WS?

I would like to be able to do this without having to type anything,
other than cell references, as if I start typing the L names I might
introduce errors, and it would be laborious.

=if(countif($c$2:c2,c2)>1,"",SUMIF(C$2:C$60,C2,J$2:J$60))



Chris wrote:
>
> I have a number of Workbooks, each containing a single Worksheet.
>
> Each WS contains similar data, i.e. uses the same column headings, but
> is of variable length.
>
> Column C in each WS is 'Location'(L1 - Lx).
>
> Column J in each WS is 'Number of Widgets' (1 - N).
>
> An L can appear several times in a WS, but not all Ls necessarily
> appear in all WSs.
>
> Every appearance of a L in a WS has a corresponding NoWs.
>
> I want to be able to sum the NoWs for each L in each WS, and get a
> single total for number of NoWs for each L in a WS.
>
> Thus far I have used
>
> '=SUMIF(C$2:C$60,C2,J$2;J$60)'
>
> and dragged this into each row in the total column. This gives me the
> correct answers, but repeats the answer for each L on every row that
> that L appears in.
>
> How can I modify this, or start again, such that I get a single total
> for each L that appears in a WS?
>
> I would like to be able to do this without having to type anything,
> other than cell references, as if I start typing the L names I might
> introduce errors, and it would be laborious.

--

Dave Peterson

You may want to consider using data|pivottable, too. You can do summaries
pretty quickly.

If you've never use pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

Chris wrote:
>
> I have a number of Workbooks, each containing a single Worksheet.
>
> Each WS contains similar data, i.e. uses the same column headings, but
> is of variable length.
>
> Column C in each WS is 'Location'(L1 - Lx).
>
> Column J in each WS is 'Number of Widgets' (1 - N).
>
> An L can appear several times in a WS, but not all Ls necessarily
> appear in all WSs.
>
> Every appearance of a L in a WS has a corresponding NoWs.
>
> I want to be able to sum the NoWs for each L in each WS, and get a
> single total for number of NoWs for each L in a WS.
>
> Thus far I have used
>
> '=SUMIF(C$2:C$60,C2,J$2;J$60)'
>
> and dragged this into each row in the total column. This gives me the
> correct answers, but repeats the answer for each L on every row that
> that L appears in.
>
> How can I modify this, or start again, such that I get a single total
> for each L that appears in a WS?
>
> I would like to be able to do this without having to type anything,
> other than cell references, as if I start typing the L names I might
> introduce errors, and it would be laborious.

--

Dave Peterson

First, would it be reasonable to create a master workbook that just contains
location names? If you wish, you could then use data validation on your
Location worksheets to keep entries restricted to what is on the master list
and ensure that new locations get added properly.

Second, you should use SUMPRODUCT to accomplish your task. The syntax is
=SUMPRODUCT((criteria1)*(criteria2)*(criteria_n)*(what_to_sum)), which in
English is just saying to add the final argument (what_to_sum) only if all
my criteria are met.

Note that the criteria and what_to_sum ranges must all be identical in size
(so if the worksheet has 422 rows of data, you would go from rows 1 to 422
for both the criteria and what_to_sum.

=SUMPRODUCT(('Location'!$C$1:$Cx='MasterLocationsList'!A1)*('Number of
Widgets'!$J$1:$Jx)). Drag this formula down for as many rows as you have
locations. For x, put in your formula to lookup the index of the last row
in the table with a value.

I think this approach is much less convoluted than trying to have something
that identifies all unique locations for you before it can carry out this
task. A master list of locations in a separate workbook makes good sense
and saves Excel a lot of calculation.

KC Rippstein

"Chris" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a number of Workbooks, each containing a single Worksheet.
>
> Each WS contains similar data, i.e. uses the same column headings, but
> is of variable length.
>
> Column C in each WS is 'Location'(L1 - Lx).
>
> Column J in each WS is 'Number of Widgets' (1 - N).
>
> An L can appear several times in a WS, but not all Ls necessarily
> appear in all WSs.
>
> Every appearance of a L in a WS has a corresponding NoWs.
>
> I want to be able to sum the NoWs for each L in each WS, and get a
> single total for number of NoWs for each L in a WS.
>
> Thus far I have used
>
> '=SUMIF(C$2:C$60,C2,J$2;J$60)'
>
> and dragged this into each row in the total column. This gives me the
> correct answers, but repeats the answer for each L on every row that
> that L appears in.
>
> How can I modify this, or start again, such that I get a single total
> for each L that appears in a WS?
>
> I would like to be able to do this without having to type anything,
> other than cell references, as if I start typing the L names I might
> introduce errors, and it would be laborious.
>

Thanks for all the suggestions, some of which have been useful, esp
having a master list.

I have been fighting shy of MS Access for sometime now, but this has
given me an opportunity to bite the bullet and I have managed to fudge
something togther using bits of Excel and bits of Access.

Thanks again to all concerned.

KC Rippstein wrote:

> First, would it be reasonable to create a master workbook that just contains
> location names? If you wish, you could then use data validation on your
> Location worksheets to keep entries restricted to what is on the master list
> and ensure that new locations get added properly.
>
> Second, you should use SUMPRODUCT to accomplish your task. The syntax is
> =SUMPRODUCT((criteria1)*(criteria2)*(criteria_n)*(what_to_sum)), which in
> English is just saying to add the final argument (what_to_sum) only if all
> my criteria are met.
>
> Note that the criteria and what_to_sum ranges must all be identical in size
> (so if the worksheet has 422 rows of data, you would go from rows 1 to 422
> for both the criteria and what_to_sum.
>
> =SUMPRODUCT(('Location'!$C$1:$Cx='MasterLocationsList'!A1)*('Number of
> Widgets'!$J$1:$Jx)). Drag this formula down for as many rows as you have
> locations. For x, put in your formula to lookup the index of the last row
> in the table with a value.
>
> I think this approach is much less convoluted than trying to have something
> that identifies all unique locations for you before it can carry out this
> task. A master list of locations in a separate workbook makes good sense
> and saves Excel a lot of calculation.
>
> KC Rippstein
>
> "Chris" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I have a number of Workbooks, each containing a single Worksheet.
> >
> > Each WS contains similar data, i.e. uses the same column headings, but
> > is of variable length.
> >
> > Column C in each WS is 'Location'(L1 - Lx).
> >
> > Column J in each WS is 'Number of Widgets' (1 - N).
> >
> > An L can appear several times in a WS, but not all Ls necessarily
> > appear in all WSs.
> >
> > Every appearance of a L in a WS has a corresponding NoWs.
> >
> > I want to be able to sum the NoWs for each L in each WS, and get a
> > single total for number of NoWs for each L in a WS.
> >
> > Thus far I have used
> >
> > '=SUMIF(C$2:C$60,C2,J$2;J$60)'
> >
> > and dragged this into each row in the total column. This gives me the
> > correct answers, but repeats the answer for each L on every row that
> > that L appears in.
> >
> > How can I modify this, or start again, such that I get a single total
> > for each L that appears in a WS?
> >
> > I would like to be able to do this without having to type anything,
> > other than cell references, as if I start typing the L names I might
> > introduce errors, and it would be laborious.
> >