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?

I am not sure what the problem is.

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.

I don't see any file.

Here you go, I think.

Attached Files

foo.xlsx (34.8 KB, 7 views)

Same remark as I gave in #2.

Attached Files

foo_2.xlsx (10.8 KB, 9 views)

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.