Extract numbers from parentheses and add them up

[pokeyarw]

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.

[xor]

Your SUMPRODUCT formula works fine for me.

[pokeyarw]

Well, that's the pits! I copied the formula directly fro the spreadsheet and it gives me zero. I did

=SUMPRODUCT(Q74:Q91)

Q74:Q91 is where I did the fill down to see that the extract was working properly. The SUMPRODUCT above gave me 84, which is correct.

The items to be summed are all entered from the keyboard, so they are not computed. I've got Office 2011 on my Mac. Is that the issue?

[xor]

I am not sure what the problem is.

[pokeyarw]

Greetings,
As indicated in the previous messages, SUMPRODUCT for Office 2011 (Mac) seems not to be bulletproof. I have attached a demonstration file. It works fine in google/sheets. All three highlighted cells have the same correct computed values. I tried it in Numbers (Mac) and cell E5 came out #VALUE, but the other two highlighted cells were correct. Considering some of the constructs I've seen using SUMPRODUCT this application seems pretty pedestrian. I don't want to use a "helper" column as I have to do this every week. Any ideas?? I post this mostly to inform others of the problem here, but I'd like to be able to get the result without creating lots of useless intermediate cells.

Column A contains a list of check numbers, sometimes with a parenthesized number. The latter is cash. I want to add the cash.
Column C is the formula which extracts the cash from the corresponding cell in Column A.
Cell D5 is the SUMPRODUCT of the array represented by Column C.
Cell C24 is the correct answer achieved by summing the column above.
Cell E5 is the SUMPRODUCT of the numbers in Column A times the 1’s in Column E just to see if the problem is that two arrays are required for SUMPRODUCT to work properly. That it gives an error is curious as the only error condition for SUMPRODUCT, given that there are no errors in Column C, seems to be that the two arrays are not the same size & shape. But they are the same size & shape.

[xor]

I don't see any file.

[pokeyarw]

Here you go, I think.

[xor]

Same remark as I gave in #2.

[pokeyarw]

I checked it out with SUM and it gives the same result (40). That seems odd. It is also odd that it works in Sheets. SUMPRODUCT seems more complex than SUM and it not working for either (on a Mac as I assume you are Windows) implies something pretty basic is not working as expected.

[xor]

I tried it out in Sheets and it works fine for me, both SUMPRODUCT and SUM. With the SUM-function you have to array-enter (hold down Ctrl and Shift before pressing Enter).