Countif, Lookup, Match ?? unsure newbie!

[jonathanscary]

Hi

I have a spreadsheet with the following data in range A1:B12 i
Worksheet 1:

North 1
North #N/A
North 4
South 2
South #N/A
South 1
South #N/A
East 1
East #N/A
West 1
West #N/A
West 2

In Worksheet 2, I have the following data in range A1:A4

North
South
East
West

In B2:B4 (Worksheet 2) I'd like to display the totals for eac
geographical area, thus:

North 5
South 2
East 1
West 3

I've been driving myself nuts all day with this. Can anybody help?

I've put a sample file in attachment.

Thank

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62359

 

[jonathanscary]

Just to be clear, the description above shows only four geographica
areas. In reality, my spreadsheet has 8,500 rows and around 80
geographical areas, so I don't want to be typing "North", "South", etc
into formulae. Hence I thought I might need to use a lookup function o
something similar

 

[Frank Kabel]

Hi
first I would try to remove the #NA formula results.
(using ISNA within this formula for example).

After this I would simply use SUMIF. e.g.
=SUMIF('sheet1'!A1:A100,A1,'sheet1'!B1:B100)

 

[Max]

Try in B1 in Sheet2:

=SUMPRODUCT(--(Sheet1!$A$1:$A$12=Sheet2!A1),Sheet1!$B$1:$B$12)

Copy B1 down to B4