I'm trying to produce a report which summarises data by country, when the raw
data is given by country - region viz:

Australia - Canberra 765.4
Australia - Melbourne 18796.3
Australia - Mobile 9290.3
Australia - Satellite 3.4
Australia - Sydney 29872

I need a function which will look up all the column B values where column A
contains 'Australia' and then sum them in a single 'Australia' cell. The sum
part is not so hugely important, I know I can work around this, but the key
function is to be able to isolate a range of data based on part of the
adjoining cell text. I can currently only make VLOOKUP isolate the value
corresponding to the 'highest' value in column A containing 'Australia' using
the following:

=VLOOKUP("Australia*",'Client Input'!A:B,2)

I think I'm barking up the right tree but I'm stuck for ideas. Please help!

Hi Jake

One way
=SUMPRODUCT((ISNUMBER(FIND("Australia",$A$1:$A$100 )))*$B$1:$B$100)

Change ranges to suit.

--
Regards
Roger Govier



"jake" wrote in message
...
I'm trying to produce a report which summarises data by country, when the
raw
data is given by country - region viz:

Australia - Canberra 765.4
Australia - Melbourne 18796.3
Australia - Mobile 9290.3
Australia - Satellite 3.4
Australia - Sydney 29872

I need a function which will look up all the column B values where column
A
contains 'Australia' and then sum them in a single 'Australia' cell. The
sum
part is not so hugely important, I know I can work around this, but the
key
function is to be able to isolate a range of data based on part of the
adjoining cell text. I can currently only make VLOOKUP isolate the value
corresponding to the 'highest' value in column A containing 'Australia'
using
the following:

=VLOOKUP("Australia*",'Client Input'!A:B,2)

I think I'm barking up the right tree but I'm stuck for ideas. Please
help!

=SUMPRODUCT(--(LEFT(A1:A5,9)="Australia"),B1:B5)
or
=SUMIF(A1:A5,"Australia*",B1:B5)


"jake" wrote:

I'm trying to produce a report which summarises data by country, when the raw
data is given by country - region viz:

Australia - Canberra 765.4
Australia - Melbourne 18796.3
Australia - Mobile 9290.3
Australia - Satellite 3.4
Australia - Sydney 29872

I need a function which will look up all the column B values where column A
contains 'Australia' and then sum them in a single 'Australia' cell. The sum
part is not so hugely important, I know I can work around this, but the key
function is to be able to isolate a range of data based on part of the
adjoining cell text. I can currently only make VLOOKUP isolate the value
corresponding to the 'highest' value in column A containing 'Australia' using
the following:

=VLOOKUP("Australia*",'Client Input'!A:B,2)

I think I'm barking up the right tree but I'm stuck for ideas. Please help!

I'm afraid there's too much barking and not enough, well... What exactly do
you want to lookup?

What is it that you actually want? It doesn't really look like you want to
'lookup' something, 'v' or otherwise. Rather, you want to sum a batch of
records based upon a criterion.

Why do you have the country and region in the same column? Split column A
into two -- Australia in A and Canberra in B for the first row. There is no
value, save lassitude, in having them combined this way.

Then, you could easily do something simple like this...

Australia Canberra 765.4
Australia Melbourne 18796.3
Australia Mobile 9290.3
Australia Satellite 3.4
Australia Sydney 29872

Australia 58727.4

Where the 58727.4 formula is =SUMIF(A1:A5,B7,C1:C5) with B7 being the word
'Australia' beside the sum. The advantage of this is that you could put any
country name you chose and get the corresponding sum.


If you insist on keeping a single column (sigh), then how about:

Australia - Canberra 765.4
Australia - Melbourne 18796.3
Australia - Mobile 9290.3
Australia - Satellite 3.4
Australia - Sydney 29872

=SUMIF(A1:A5,"Australia*",B1:B5)


On 9/13/07 12:42 PM, in article
, "jake"
wrote:

I'm trying to produce a report which summarises data by country, when the raw
data is given by country - region viz:

Australia - Canberra 765.4
Australia - Melbourne 18796.3
Australia - Mobile 9290.3
Australia - Satellite 3.4
Australia - Sydney 29872

I need a function which will look up all the column B values where column A
contains 'Australia' and then sum them in a single 'Australia' cell. The sum
part is not so hugely important, I know I can work around this, but the key
function is to be able to isolate a range of data based on part of the
adjoining cell text. I can currently only make VLOOKUP isolate the value
corresponding to the 'highest' value in column A containing 'Australia' using
the following:

=VLOOKUP("Australia*",'Client Input'!A:B,2)

I think I'm barking up the right tree but I'm stuck for ideas. Please help!