Forum Discussion
Help with formula that sums first digit if the string contains certain characters.
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 Replies
Hi Noel,
That could be array formula (Ctrl+Shift+Enter)
=SUM(IF(ISNUMBER(SEARCH("(151)",B97:B103)),VALUE(LEFT(B97:B103,2)),0))Duuuuuuuddddeeeee that was perfect!
Thanks a lot! You made my day and saved us hours of work.
