Looking up data with varying Lookup_value
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!
|