Help with formula that sums first digit if the string contains certain characters.

[Noel Robles]

Hello community, I'm trying to make a formula that does this specifically.

Example data

Amount - Item Code and Name (COLUMN B, ROWS 97 TO 103)

1 - (151) item 01

3 - (157) item 02

10 - (001) item 03

15 - (151) item 01

As you can see in that group there's a total of 16 of (151) item 1, I want to create a formula that looks for strings containing the code "(151) and SUM the first 2 digits of those strings.

The closest ive been is to use SUMPRODUCT(--(LEFT(B97:B103,2))) but that gives me a total of the whole list (which would be 29) I want a formula or VBA code or whatever that looks for strings that "has" (151) somewhere in the string and take the first 2 numbers and SUM them.

Not sure if that makes a lot of sense but any help is appreciated, thank you.

[#2]

Hi Noel,

That could be array formula (Ctrl+Shift+Enter)

=SUM(IF(ISNUMBER(SEARCH("(151)",B97:B103)),VALUE(LEFT(B97:B103,2)),0))

[#3]

Duuuuuuuddddeeeee that was perfect!

Thanks a lot! You made my day and saved us hours of work.