Hi all, first post here.

I've got a problem with a spreadsheet where SUMPRODUCT is mostly working, but sometimes returns a 0, even when I know it shouldn't. I've done a search, and found useful help here, but this isn't a problem with the formula...it's something else. Here's what's happening, with examples.

The first column I've got contains locations. The second column contains either the words "Male" or "Female". The locations are a variety of "Basingstoke", "East Hants", "Eastleigh", "Winchester", "New Forest", plus a few others. The locations are in column C, from row 2 to 1031. The Gender is in column D, in the same rows.

Now, the following formula always produces 0, even though I know there are some occurrences of "Basingstoke" and "Male" being in the same row.

=SUMPRODUCT(($C$2:$C$1031="basingstoke")*($D$2:$D$ 1031="male"))

However, this other formula works!

=SUMPRODUCT(($C$2:$C$1031="winchester")*($D$2:$D$1 031="male"))

(P.S. The spaces that are showing in the latter part of these formulae aren't actually there in the spreadsheet, or when I typed this...the forum seems to be adding them by itself!)

(These are copied straight from the spreadsheet...I can't for the life of me see what's happening. The formula doesn't work in the spreadsheet for "Basingstoke" or "Eastleigh", but does work for "East Hants" and "Winchester".

I tried copying the entire worksheet to a new Excel workbook, using the destination formatting, but had the same problem. I manually created a new list with the words "Basingstoke" and "Eastleigh", and it did work, (just discounting the slim possibility that these were reserved words or something!)

Does anyone have any ideas on what might be causing this anomaly? It's Excel 2003 by the way, running on Windows 2000 over Citrix PS4, if that makes any difference.

Thanks in advance for any, (and I mean ANY), suggestions...it's driving me nuts. :)

Tim.


EDIT: Okay, I've just found out that if I type the word "Basingstoke" in a cell in, say, column G, then copy and paste this over any occurrence of "Basingstoke" in column C, the formula starts working. However, I can see no difference in the Cell Formatting between one of the original occurrences of "Basingstoke" and one of the new occurrences.