SUMPRODUCT returns 0, when I know it shouldn't

[Tim Bridle]

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$1031="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.

 

[VBANoob]

Try

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

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA Noob

 

[Chip Pearson]

Try

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

In this case, the "--" is not required since the '*' operator will already
convert booleans to numbers.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[VBANoob]

Thanks Chip for clarifying

VBA Noob

Try

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

In this case, the "--" is not required since the '*' operator will already
convert booleans to numbers.

Try

[quoted text clipped - 58 lines]