Lookup part of a cell in Range

[warpsys]

Hi,

I am trying to find a formula that will look up the first fe
characters to see if there is a match and then sum the numbers in
corresonding column.

Column A has a mix of numbers and text, in a certain format and Colum
G has numbers. The format of Column A is

1100 England
1100 Scotland
1115 Wales
1112 Ireland
1155 France

Hopefully, the formula will Look Up the rows in Column A that contai
1100 at the start and add the number in the corresponding Column G to
Sum Total.

Is that possible ?

Many thanks

Pau

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(ISNUMBER(FIND("1110",A1:A20))),B1:B20)

 

[warpsys]

Excellent, thank you

Just out of curiosity, what does the (-- do ?

Thanks

Pau

 

[JE McGimpsey]

ISNUMBER() returns an array of boolean (TRUE/FALSE) values. SUMPRODUCT()
requires numeric values, so one - sign coerces the TRUE/FALSEs to -1/0s,
respectively. The second - negates that array to produce 1/0s.