Greetings,
I’d like to extract some parenthesized numbers from a column of cells. E.g.
6031, Cash(40)
Cash(44)
2722
Cash
5887
1985
1670
1246, 1245
1398
I can extract the amount of cash with
=VALUE(IFERROR(MID(Q5,SEARCH("(",Q5,1)+1,SEARCH(") ",Q5,1)-SEARCH("(",Q5,1)-1),0))
which works just fine, but I don’t really want the individual values, I want the sum. If I copy fill this down it produces all the right numbers. There’s probably a more elegant way to do this, but this works.
But I really want the total and it seems like a sumproduct or just a sum should give me the sum but it gives me zero, e.g.,
=SUMPRODUCT((IFERROR(VALUE(MID(Q5:Q33,SEARCH("(",Q 5:Q33,1)+1,SEARCH(")",Q5:Q33,1)-SEARCH("(",Q5:Q33,1)-1)),0)))
I’m obviously working in column Q and the array I want to sum is Q5:Q33.
What am I missing?
For reasons I don't understand, the published version of this post shows a space between the Q and the 5 in the first SEARCH looking for the "(". It's not there in my usage.