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.

Try this:

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

If that works, then the basingstoke cells have leading and/or trailing spaces.
eg "basingstoke " versus "basingstoke"

Post back with more questions.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Tim Bridle" wrote:


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.




--
Tim Bridle

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


"Tim Bridle" wrote:


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.




--
Tim Bridle

Try

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

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

VBA Noob

Tim Bridle wrote:
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" <u31102@uwe wrote in message news:6c9141ecd6c3f@uwe...
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" <u31102@uwe wrote in message news:6c9141ecd6c3f@uwe...
Try

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

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

VBA Noob

Tim Bridle wrote:
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.

Thanks Chip for clarifying

VBA Noob

Chip Pearson wrote:
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]
the original occurrences of "Basingstoke" and one of the new
occurrences.