Formulas Between Different Sheets

[rtidrtid]

Hi there,

I have a worksheet like the below

Column A Column B

London 1
Manchester 2
London 3
Derby 3
London 3
Manchester 3

I have a 2nd worksheet with all the names in column A. However I wan
the total of column B for each London to be in the 2nd worksheet.

For example, on the 2nd worksheet it would have

London 7
Derby 3
Manchester 5

Any ideas

 

[Guest]

Hi

I would suggest using a pivot table - which is for summarising data.
Have a look here for an intro on how to use them
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Hope this helps.
Andy.

 

[Dave O]

Take a look at the SUMIF() function: it examines a range to find exact
matches, and sums the elements from a different range when there is a
match. The caveat is the spelling must be exact: "London" will not
match to "Lon don" or "London " . Note the space at the end of the
last example: "London " with a space will appear to be the same as
"London" with no space, but the computer sees the distinction.

 

[flummi]

Data in A1:B6 like you suggested.

Have the names for the totals in e.g. a8, a9 etc

In B8 enter: =SUMIF($A$1:$A$6;"="&A8;$B$1:$B$6)

Copy the formula down as required.

If you want to remove blanks in your names create entries in C1:C6 and
use this formula in C1

=SUBSTITUTE(A1;" ";"")

copy down to C6

and base your sumif command on the new column like:

=SUMIF($C$1:$C$6;"="&A8;$B$1:$B$6)

Hans