Hi folks.
I use sumproduct to sum columns of values after testing against 2 criteria.
At first it was working great, and then it stopped working once one cell
that I was testing against had a value in it. It's actually become quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and cell b
would be the values that I'm looking for; and the Col&RowRangeFinal would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone so far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors, or #ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd hate to be
forced to use something else to solve my reasons for using it to begin with.

Hi Steve
Post your actual formula, and a sample of what data appears in the
column you are testing against cell a, what data is being tested against
cell b and what data exists in Final.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Hi folks.
I use sumproduct to sum columns of values after testing against 2
criteria.
At first it was working great, and then it stopped working once one
cell
that I was testing against had a value in it. It's actually become
quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and
cell b
would be the values that I'm looking for; and the Col&RowRangeFinal
would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone
so far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be
really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors, or
#ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd hate
to be
forced to use something else to solve my reasons for using it to begin
with.

Roger,
Here is a sample of actual formula, and data.
=sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30))
Map! is the worksheet name
Column F = John Doe, Sam Jack, etc.... (all names of clients)
Column C = same names as Col. F. It however is on another sheet, and is
searching data on that sheet
Column E = 5 digit numeric values (we classify these as "general" format.
These are permit #'s. I say general format because we just want plain numbers
with no decimal places.)
Column A = same permit #'s as in E. (Same as in C. The data being searched
is on another worksheet.)
Column B is acreage values.
This is a Civil Engineering application, tracking ownership of land, and
water rights.
As mentioned initially it worked really well in the beginning. After a few
weeks it began to become finicky.
To give some more info, I've saved the general form of the equation into an
*.xlt template in my template directory. I adjust the row range for the
specific worksheet length.
And finally, I've even deleted columns, cleared formatting, etc... to wipe
out any possible corruption that may have defiled the file I work with.
I can open a workbook that has never used the formula, and it may, or may
not work there. One of the engineers even did a test before he started using
it, and tried a variety of values. He was unable to get it to work. I just
applied it to my worksheet set, and found it to work immediately. It was only
later that it began not working sporadically. Now I'm working on one workbook
in which any of the values with a permit number return a 0.
I get that what I'm asking is for the routine to search the column F range
on the Map worksheet, and compare it to the data in cell C3 on the other
worksheet. IF that data exists, it will return a true, or 1. It then looks
through all of the data in the Column E range on the Map worksheet, and
compares it to the data in cell A3. If the data is there, it returns a true,
or 1. If the data is NOT there, it returns a false, or 0. OR, if the data is
in one of the cells, and not the other, it will then return false, or 0. For
the values that are both true, it will return a true, or 1, and then add the
third column set to return a total value.
Thus giving me a dataset comparable to
true*true*4.5 = 4.5
true*false*2.3 = 0
false*false*2.1 = 0
false*true*1.2 = 0
true*true*6.5 = 6.5
total = 11.00
Let me know if you need more.


"Roger Govier" wrote:

Hi Steve
Post your actual formula, and a sample of what data appears in the
column you are testing against cell a, what data is being tested against
cell b and what data exists in Final.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Hi folks.
I use sumproduct to sum columns of values after testing against 2
criteria.
At first it was working great, and then it stopped working once one
cell
that I was testing against had a value in it. It's actually become
quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and
cell b
would be the values that I'm looking for; and the Col&RowRangeFinal
would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone
so far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be
really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors, or
#ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd hate
to be
forced to use something else to solve my reasons for using it to begin
with.

Hi Steve

I can see nothing that is implicitly wrong with your formula. Your
reasoning at the end of your email is absolutely correct and that is how
Sumproduct should arrive at your answer. The fault therefore, I believe
must lie with the data.

Firstly, your Numeric values in column B are they Numeric or could they
be text representations of the numbers.
Test the values by using a spare column and entering =SUM(B4:B30). Does
it give the correct result or 0?
If it gives 0, then you could try entering a 1 in another cell, copy it,
mark B4:B30 and Paste SpecialMultiply. That should coerce the values
from text to numeric.

Next test the Permit Numbers. Are they Numbers or Text in MAP!$E and is
it a Text or numeric value in A3?
Finally, test the names in Map!F, against the value in C3. Try looking
at a cell in MAP!F4:F30 that has the same value as you have in C3 and
calculate say =LEN(MAP!F4) and =LEN(C3). Are the results the same? If
not there may be leading or trailing spaces in either the data or the
comparative cell.

Apart from the space character Char(32), if the data were copied from
another source, it might also contain the Non breaking space character
Char(160).

In a spare column, enter =SUBSTITUTE(SUBSTITUTE(E4,CHAR(160),"")," ","")
and copy down for the length of your data range. Then, copy this new
column, and Paste SpecialValues back over the original data in E4:E30.

Also, if you are using XL2003, highlight your formula and go to
ToolsFormula AuditingEvaluate Formula and step through the formula and
you will see in the white pane how Excel interprets each part of the
equation in building to an answer.

If none of these tests bring success, post back and maybe someone else
will have further thoughts.


--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Roger,
Here is a sample of actual formula, and data.
=sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30))
Map! is the worksheet name
Column F = John Doe, Sam Jack, etc.... (all names of clients)
Column C = same names as Col. F. It however is on another sheet, and
is
searching data on that sheet
Column E = 5 digit numeric values (we classify these as "general"
format.
These are permit #'s. I say general format because we just want plain
numbers
with no decimal places.)
Column A = same permit #'s as in E. (Same as in C. The data being
searched
is on another worksheet.)
Column B is acreage values.
This is a Civil Engineering application, tracking ownership of land,
and
water rights.
As mentioned initially it worked really well in the beginning. After
a few
weeks it began to become finicky.
To give some more info, I've saved the general form of the equation
into an
*.xlt template in my template directory. I adjust the row range for
the
specific worksheet length.
And finally, I've even deleted columns, cleared formatting, etc... to
wipe
out any possible corruption that may have defiled the file I work
with.
I can open a workbook that has never used the formula, and it may, or
may
not work there. One of the engineers even did a test before he started
using
it, and tried a variety of values. He was unable to get it to work. I
just
applied it to my worksheet set, and found it to work immediately. It
was only
later that it began not working sporadically. Now I'm working on one
workbook
in which any of the values with a permit number return a 0.
I get that what I'm asking is for the routine to search the column F
range
on the Map worksheet, and compare it to the data in cell C3 on the
other
worksheet. IF that data exists, it will return a true, or 1. It then
looks
through all of the data in the Column E range on the Map worksheet,
and
compares it to the data in cell A3. If the data is there, it returns a
true,
or 1. If the data is NOT there, it returns a false, or 0. OR, if the
data is
in one of the cells, and not the other, it will then return false, or
0. For
the values that are both true, it will return a true, or 1, and then
add the
third column set to return a total value.
Thus giving me a dataset comparable to
true*true*4.5 = 4.5
true*false*2.3 = 0
false*false*2.1 = 0
false*true*1.2 = 0
true*true*6.5 = 6.5
total = 11.00
Let me know if you need more.


"Roger Govier" wrote:

Hi Steve
Post your actual formula, and a sample of what data appears in the
column you are testing against cell a, what data is being tested
against
cell b and what data exists in Final.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Hi folks.
I use sumproduct to sum columns of values after testing against 2
criteria.
At first it was working great, and then it stopped working once one
cell
that I was testing against had a value in it. It's actually become
quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a,
and
cell b
would be the values that I'm looking for; and the Col&RowRangeFinal
would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've
gone
so far
as to copy and paste cell values from the source regions to the
final
regions, and it still only works sometimes.) If so, why? It'd be
really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors,
or
#ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd
hate
to be
forced to use something else to solve my reasons for using it to
begin
with.

Maybe this will make a dif?
Column C = same names as Col. F. It however is on another sheet, and is
searching data on that sheet
Map!$F$4:$F$30=$C3
Map!$F$4:$F$30=sheet3!$C3

this may also help
)*(Map$B$3:$B$30))
-)*(Map!$B$3:$B$30))-

Don Guillett
SalesAid Software

"SteveDB1" wrote in message
...
Roger,
Here is a sample of actual formula, and data.
=sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30))
Map! is the worksheet name
Column F = John Doe, Sam Jack, etc.... (all names of clients)
Column C = same names as Col. F. It however is on another sheet, and is
searching data on that sheet
Column E = 5 digit numeric values (we classify these as "general" format.
These are permit #'s. I say general format because we just want plain
numbers
with no decimal places.)
Column A = same permit #'s as in E. (Same as in C. The data being searched
is on another worksheet.)
Column B is acreage values.
This is a Civil Engineering application, tracking ownership of land, and
water rights.
As mentioned initially it worked really well in the beginning. After a
few
weeks it began to become finicky.
To give some more info, I've saved the general form of the equation into
an
*.xlt template in my template directory. I adjust the row range for the
specific worksheet length.
And finally, I've even deleted columns, cleared formatting, etc... to wipe
out any possible corruption that may have defiled the file I work with.
I can open a workbook that has never used the formula, and it may, or may
not work there. One of the engineers even did a test before he started
using
it, and tried a variety of values. He was unable to get it to work. I just
applied it to my worksheet set, and found it to work immediately. It was
only
later that it began not working sporadically. Now I'm working on one
workbook
in which any of the values with a permit number return a 0.
I get that what I'm asking is for the routine to search the column F range
on the Map worksheet, and compare it to the data in cell C3 on the other
worksheet. IF that data exists, it will return a true, or 1. It then looks
through all of the data in the Column E range on the Map worksheet, and
compares it to the data in cell A3. If the data is there, it returns a
true,
or 1. If the data is NOT there, it returns a false, or 0. OR, if the data
is
in one of the cells, and not the other, it will then return false, or 0.
For
the values that are both true, it will return a true, or 1, and then add
the
third column set to return a total value.
Thus giving me a dataset comparable to
true*true*4.5 = 4.5
true*false*2.3 = 0
false*false*2.1 = 0
false*true*1.2 = 0
true*true*6.5 = 6.5
total = 11.00
Let me know if you need more.


"Roger Govier" wrote:

Hi Steve
Post your actual formula, and a sample of what data appears in the
column you are testing against cell a, what data is being tested against
cell b and what data exists in Final.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Hi folks.
I use sumproduct to sum columns of values after testing against 2
criteria.
At first it was working great, and then it stopped working once one
cell
that I was testing against had a value in it. It's actually become
quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and
cell b
would be the values that I'm looking for; and the Col&RowRangeFinal
would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone
so far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be
really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors, or
#ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd hate
to be
forced to use something else to solve my reasons for using it to begin
with.

Roger,
Thanks for your reply.
Ok....
Map!E column is indeed showing up with a value when I sum the permit values.
However, the "other" page permit values add up to zero. I then went into
make sure that the formatting was correct. It shows as general. I then
checked the cell that I did the sum formula. It was formatted as text. I
reset the formatting to number, with zero decimal places. It still remained
at zero. I then did as you mentioned, and made each cell an equation. i.e.,
=1*permit#. My sumproduct cells then changed to the appropriate values. I
then removed the equation component, and just left the Permit #'s in each
cell, and the values over in the sumproduct column remained. It apepars to
have "forced" the solution, and then just left it alone once it was changed
to only the permit numbers by removing the =1* portion.
At this point I spoke with the other engineer I made mention of, and he said
that this was odd, and that the sumproduct should be able to just read the
values in the cells being tested, and not look at formatting, or anything
else. And quite frankly, I agree with him.
What does it take to make sufficient modifications to the general form of
the equation within the background of excel to have it look only at cell
contents, and not any other aspect or property of that cell?
This is an excellent routine, but its ruined by its looking at all the
properties of the cell, instead of just the contents. Is there something that
can be done on MS' side to modify this, or ....?
Again, thank you.
and you too Don.

"Roger Govier" wrote:

Hi Steve

I can see nothing that is implicitly wrong with your formula. Your
reasoning at the end of your email is absolutely correct and that is how
Sumproduct should arrive at your answer. The fault therefore, I believe
must lie with the data.

Firstly, your Numeric values in column B are they Numeric or could they
be text representations of the numbers.
Test the values by using a spare column and entering =SUM(B4:B30). Does
it give the correct result or 0?
If it gives 0, then you could try entering a 1 in another cell, copy it,
mark B4:B30 and Paste SpecialMultiply. That should coerce the values
from text to numeric.

Next test the Permit Numbers. Are they Numbers or Text in MAP!$E and is
it a Text or numeric value in A3?
Finally, test the names in Map!F, against the value in C3. Try looking
at a cell in MAP!F4:F30 that has the same value as you have in C3 and
calculate say =LEN(MAP!F4) and =LEN(C3). Are the results the same? If
not there may be leading or trailing spaces in either the data or the
comparative cell.

Apart from the space character Char(32), if the data were copied from
another source, it might also contain the Non breaking space character
Char(160).

In a spare column, enter =SUBSTITUTE(SUBSTITUTE(E4,CHAR(160),"")," ","")
and copy down for the length of your data range. Then, copy this new
column, and Paste SpecialValues back over the original data in E4:E30.

Also, if you are using XL2003, highlight your formula and go to
ToolsFormula AuditingEvaluate Formula and step through the formula and
you will see in the white pane how Excel interprets each part of the
equation in building to an answer.

If none of these tests bring success, post back and maybe someone else
will have further thoughts.


--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Roger,
Here is a sample of actual formula, and data.
=sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30))
Map! is the worksheet name
Column F = John Doe, Sam Jack, etc.... (all names of clients)
Column C = same names as Col. F. It however is on another sheet, and
is
searching data on that sheet
Column E = 5 digit numeric values (we classify these as "general"
format.
These are permit #'s. I say general format because we just want plain
numbers
with no decimal places.)
Column A = same permit #'s as in E. (Same as in C. The data being
searched
is on another worksheet.)
Column B is acreage values.
This is a Civil Engineering application, tracking ownership of land,
and
water rights.
As mentioned initially it worked really well in the beginning. After
a few
weeks it began to become finicky.
To give some more info, I've saved the general form of the equation
into an
*.xlt template in my template directory. I adjust the row range for
the
specific worksheet length.
And finally, I've even deleted columns, cleared formatting, etc... to
wipe
out any possible corruption that may have defiled the file I work
with.
I can open a workbook that has never used the formula, and it may, or
may
not work there. One of the engineers even did a test before he started
using
it, and tried a variety of values. He was unable to get it to work. I
just
applied it to my worksheet set, and found it to work immediately. It
was only
later that it began not working sporadically. Now I'm working on one
workbook
in which any of the values with a permit number return a 0.
I get that what I'm asking is for the routine to search the column F
range
on the Map worksheet, and compare it to the data in cell C3 on the
other
worksheet. IF that data exists, it will return a true, or 1. It then
looks
through all of the data in the Column E range on the Map worksheet,
and
compares it to the data in cell A3. If the data is there, it returns a
true,
or 1. If the data is NOT there, it returns a false, or 0. OR, if the
data is
in one of the cells, and not the other, it will then return false, or
0. For
the values that are both true, it will return a true, or 1, and then
add the
third column set to return a total value.
Thus giving me a dataset comparable to
true*true*4.5 = 4.5
true*false*2.3 = 0
false*false*2.1 = 0
false*true*1.2 = 0
true*true*6.5 = 6.5
total = 11.00
Let me know if you need more.


"Roger Govier" wrote:

Hi Steve
Post your actual formula, and a sample of what data appears in the
column you are testing against cell a, what data is being tested
against
cell b and what data exists in Final.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Hi folks.
I use sumproduct to sum columns of values after testing against 2
criteria.
At first it was working great, and then it stopped working once one
cell
that I was testing against had a value in it. It's actually become
quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a,
and
cell b
would be the values that I'm looking for; and the Col&RowRangeFinal
would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've
gone
so far
as to copy and paste cell values from the source regions to the
final
regions, and it still only works sometimes.) If so, why? It'd be
really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors,
or
#ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd
hate
to be
forced to use something else to solve my reasons for using it to
begin
with.

Hi Steve

Glad you got it working.
No, there is nothing wrong with the behaviour of Excel or Sumproduct.
How values are stored internally by Excel is different to how they are
displayed or portrayed to the user through formatting.

For example, if you enter 17/11/2006 in a cell (UK date format) it will
be stored by Excel as the number of days since 31 12 1899 which is 39038
a numeric value. It will display as 17/11/2006 which looks like text, or
through formatting, you can change the appearance to 17 Nov 2006 or 17
November 2006 or November or a variety of different things. None of
these "cosmetic" changes will affect how the value is stored 39038.

Now if you enter '17 Nov 2006 it will display as 17 Nov 2006 and will be
stored as a string "17 Nov 2006". Excel will allow you to enter ' 31 Nov
2006 as it is just a string or '17 Roger's Month 2006 but trying to
enter these latter two as dates would be rejected as they are invalid.

If Excel did a comparison between a cell holding 17 Nov 2006 (true Excel
date) and '17 Nov 2006 it would quite rightly say that the match was
False, as it is comparing a numeric value 39038 with a string "17 Nov
2006"

Changing the format of a cell, does not necessarily alter any contents
already entered, but will treat any entries made after formatting in the
manner required.

If in a cell formatted General (the default setting), you enter a 1, it
will be treated as numeric and you will see the number in the cell right
justified. If you prefix the entry with a single quote '1 the quote will
not show in the cell, and the number will be a text 1 and will be left
justified and will be stored as the string "1".
If you format the cell as Text, then enter a 1 without any preceding
quote, then it will be treated as Text and stored as the string "1"
If your reference numbers were greater than 15 digits in length, then
they would have to entered as Text, as Excel only supports 15 digits for
numerical entry.

Now, in Sumproduct, you were asking it to do comparisons of each cell in
the range E4:E30 with the value in cell A3.
If A3 had 123456 as a number and E4 had "123456" as text, then they are
not the same and a False is returned
If E4:E30 were text values, then provided your entry in A3 was a text
value, all would have worked.

I didn't actually suggest that you converted those values to numeric, it
was the acreages in column B that "had" to be numeric, otherwise they
could not be summed.
I just suggested that you checked that both the values were text or
numeric although I perhaps had not made this clear enough. Provided they
are both of the same format, it does not matter what the format is, then
the comparison can be made as to whether they are equal to each other or
not.

The original entries may have been copied and pasted from another
source, which was text and that is why they will have been text. Or,
there may be some values which are greater than 15 digits, which would
therefore have required them all to be entered as Text.

If any were greater than 16 digits, they will now be incorrect.
1234567890123456789 would be changed to 1234567890123450000 when you
multiplied by 1.

Make sure you check this out. If they are greater than 15, you will need
to correct the column back to text and type the final digits again, and
change cell A3 to text, so when you enter the value you wish to be
compared it is a text format.

I hope this clarifies things.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Roger,
Thanks for your reply.
Ok....
Map!E column is indeed showing up with a value when I sum the permit
values.
However, the "other" page permit values add up to zero. I then went
into
make sure that the formatting was correct. It shows as general. I then
checked the cell that I did the sum formula. It was formatted as text.
I
reset the formatting to number, with zero decimal places. It still
remained
at zero. I then did as you mentioned, and made each cell an equation.
i.e.,
=1*permit#. My sumproduct cells then changed to the appropriate
values. I
then removed the equation component, and just left the Permit #'s in
each
cell, and the values over in the sumproduct column remained. It
apepars to
have "forced" the solution, and then just left it alone once it was
changed
to only the permit numbers by removing the =1* portion.
At this point I spoke with the other engineer I made mention of, and
he said
that this was odd, and that the sumproduct should be able to just read
the
values in the cells being tested, and not look at formatting, or
anything
else. And quite frankly, I agree with him.
What does it take to make sufficient modifications to the general form
of
the equation within the background of excel to have it look only at
cell
contents, and not any other aspect or property of that cell?
This is an excellent routine, but its ruined by its looking at all the
properties of the cell, instead of just the contents. Is there
something that
can be done on MS' side to modify this, or ....?
Again, thank you.
and you too Don.

"Roger Govier" wrote:

Hi Steve

I can see nothing that is implicitly wrong with your formula. Your
reasoning at the end of your email is absolutely correct and that is
how
Sumproduct should arrive at your answer. The fault therefore, I
believe
must lie with the data.

Firstly, your Numeric values in column B are they Numeric or could
they
be text representations of the numbers.
Test the values by using a spare column and entering =SUM(B4:B30).
Does
it give the correct result or 0?
If it gives 0, then you could try entering a 1 in another cell, copy
it,
mark B4:B30 and Paste SpecialMultiply. That should coerce the values
from text to numeric.

Next test the Permit Numbers. Are they Numbers or Text in MAP!$E and
is
it a Text or numeric value in A3?
Finally, test the names in Map!F, against the value in C3. Try
looking
at a cell in MAP!F4:F30 that has the same value as you have in C3 and
calculate say =LEN(MAP!F4) and =LEN(C3). Are the results the same?
If
not there may be leading or trailing spaces in either the data or the
comparative cell.

Apart from the space character Char(32), if the data were copied from
another source, it might also contain the Non breaking space
character
Char(160).

In a spare column, enter =SUBSTITUTE(SUBSTITUTE(E4,CHAR(160),""),"
","")
and copy down for the length of your data range. Then, copy this new
column, and Paste SpecialValues back over the original data in
E4:E30.

Also, if you are using XL2003, highlight your formula and go to
ToolsFormula AuditingEvaluate Formula and step through the formula
and
you will see in the white pane how Excel interprets each part of the
equation in building to an answer.

If none of these tests bring success, post back and maybe someone
else
will have further thoughts.


--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Roger,
Here is a sample of actual formula, and data.
=sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30))
Map! is the worksheet name
Column F = John Doe, Sam Jack, etc.... (all names of clients)
Column C = same names as Col. F. It however is on another sheet,
and
is
searching data on that sheet
Column E = 5 digit numeric values (we classify these as "general"
format.
These are permit #'s. I say general format because we just want
plain
numbers
with no decimal places.)
Column A = same permit #'s as in E. (Same as in C. The data being
searched
is on another worksheet.)
Column B is acreage values.
This is a Civil Engineering application, tracking ownership of
land,
and
water rights.
As mentioned initially it worked really well in the beginning.
After
a few
weeks it began to become finicky.
To give some more info, I've saved the general form of the equation
into an
*.xlt template in my template directory. I adjust the row range for
the
specific worksheet length.
And finally, I've even deleted columns, cleared formatting, etc...
to
wipe
out any possible corruption that may have defiled the file I work
with.
I can open a workbook that has never used the formula, and it may,
or
may
not work there. One of the engineers even did a test before he
started
using
it, and tried a variety of values. He was unable to get it to work.
I
just
applied it to my worksheet set, and found it to work immediately.
It
was only
later that it began not working sporadically. Now I'm working on
one
workbook
in which any of the values with a permit number return a 0.
I get that what I'm asking is for the routine to search the column
F
range
on the Map worksheet, and compare it to the data in cell C3 on the
other
worksheet. IF that data exists, it will return a true, or 1. It
then
looks
through all of the data in the Column E range on the Map worksheet,
and
compares it to the data in cell A3. If the data is there, it
returns a
true,
or 1. If the data is NOT there, it returns a false, or 0. OR, if
the
data is
in one of the cells, and not the other, it will then return false,
or
0. For
the values that are both true, it will return a true, or 1, and
then
add the
third column set to return a total value.
Thus giving me a dataset comparable to
true*true*4.5 = 4.5
true*false*2.3 = 0
false*false*2.1 = 0
false*true*1.2 = 0
true*true*6.5 = 6.5
total = 11.00
Let me know if you need more.


"Roger Govier" wrote:

Hi Steve
Post your actual formula, and a sample of what data appears in the
column you are testing against cell a, what data is being tested
against
cell b and what data exists in Final.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Hi folks.
I use sumproduct to sum columns of values after testing against
2
criteria.
At first it was working great, and then it stopped working once
one
cell
that I was testing against had a value in it. It's actually
become
quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a,
and
cell b
would be the values that I'm looking for; and the
Col&RowRangeFinal
would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've
gone
so far
as to copy and paste cell values from the source regions to the
final
regions, and it still only works sometimes.) If so, why? It'd be
really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the
office
assistant help file. That killed everything with no sign of
errors,
or
#ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so
I'd
hate
to be
forced to use something else to solve my reasons for using it to
begin
with.

Ok. upon further investigation, and various "trials" I've found that by
"forcing" a solution, by means of inputting a value, such as =0, or
=1*cell_contents I can obtain the correct values.
To me, and others I've mentioned this to, it makes no sense.
There are times that I can have identical cell contents for different cells,
and it will not recognize them. As such it will give me "individual" totals,
instead of summing the contents as is sought. If I take one of the cell's
values, and "coerce it" the different values will all be recognised under
each of the cells that the sumproduct formula is in, and I then must remove
the duplicates to get my total.
All of this brings me back to the issue of why doesn't sumproduct just look
at the cell contents, instead of the formatting, or other properties of that
cell?
What does it take to get it to always look at JUST THE CELL CONTENTS, and
none of the other properties-- unless I specify those specific properties?
This formula will work on the source page, but when I go to move it to a
secondary, or tertiary page it will not recognize the cells being tested
against due to these issues. grrrrr!
Somewhere in the background I can hear Elmer Fudd saying, "scwewy wabbit!!!"


"SteveDB1" wrote:

Hi folks.
I use sumproduct to sum columns of values after testing against 2 criteria.
At first it was working great, and then it stopped working once one cell
that I was testing against had a value in it. It's actually become quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and cell b
would be the values that I'm looking for; and the Col&RowRangeFinal would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone so far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors, or #ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd hate to be
forced to use something else to solve my reasons for using it to begin with.

SteveDB1 wrote...
....
Here is a sample of actual formula, and data.
=sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30))
....

1st and 2nd ranges run from row 4 to row 30, but 3rd range (note the
typo: missing ! between Map and $B) runs from row 3 to row 30. The
product of the terms inside SUMPRODUCT will generate an #N/A as the
last item in the result array. The missing ! will prevent Excel from
even allowing you to enter it (so you didn't paste in your actual
formula, did you?), but that's presumably not the problem you're
having. Once you correct that typo, the mismatched rows would cause
SUMPRODUCT to return #N/A rather than 0.

SteveDB1 wrote...
Ok. upon further investigation, and various "trials" I've found that by
"forcing" a solution, by means of inputting a value, such as =0, or
=1*cell_contents I can obtain the correct values.

This means the cells' original contents evaluated as text rather than
numbers. That so, changing number format would have no effect
whatsoever on the values in those cells. Also, as others have pointed
out, Excel will treat numbers and text that might look the same as
different values, e.g.,

=(12345="12345")

will return FALSE.

To me, and others I've mentioned this to, it makes no sense.

Because you're failing to understand that in Excel text values never
equal numeric values even if they appear identical. The data type
difference matters, appearance doesn't.

There are times that I can have identical cell contents for different cells,
....

They only APPEAR identical, but they're not. If you believe you're only
working with numbers, then force all values to numbers.

=SUMPRODUCT((-range1=-cellX)*(-range2=-cellY)*range3)

values, and "coerce it" the different values will all be recognised under
each of the cells that the sumproduct formula is in, and I then must remove
the duplicates to get my total.

No, just coerce them within the SUMPRODUCT call, as in the formula
above.

All of this brings me back to the issue of why doesn't sumproduct just look
at the cell contents, instead of the formatting, or other properties of that
cell?
....

It does. YOU just have to understand that Excel provides no visible
cues to distinguish cells containing text from cells containing
numbers. You may have cells that APPEAR to contain numbers, but they
COULD contain text. Since there are times when it could be useful for
cells to contain text composed of nothing but decimal numerals, Excel's
ability to distinguish between the number 12345 and the text string
12345 is NOT a flaw. It's just a latent opportunity for you to learn
something more about Excel.

IOW, Excel *IS* just using the cell contents, but Excel makes a
distinction between different data types that merely appear to have the
same value when displayed as text.

What does it take to get it to always look at JUST THE CELL CONTENTS, and
none of the other properties-- unless I specify those specific properties?
....

Again, see the formula above. It's *YOUR* permit numbers that aren't
numeric. If you want them to be, then it's *YOUR* responsibility to
make them so. Alternatively, compare them to text values. So either

(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Ok, Harlan,
I tried the last two equations you mentioned, and the first one does not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")


So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go through and
find out why one equation works and the very next one does not work.
My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going through
looking for small, inconspicuous reasons for its failure. I came looking to
learn more about this equation because it did once work, and it sped up my
work process by an undefinable amount of time. It's now taking 3 x's longer
to figure out why it's not working.
You're saying that one column could be being recognized as text strings,
when the other column its comparing to could be being recognized as numeric
values, and as such it returns a false because the text string is not
recognized the same as a numeric value.
This then takes me back to the point of WHY?
I don't care what the value is. I don't care that 12345 is numeric in one
cell, and a text string in the other column. I just want it to be recognized
as being the same, because regardless of the format/properties behind each,
it is still just 12345.
I entered it as JUST 12345. I did not enter it as "12345" in one column, and
12345 in the other. Nor did I enter it as <12345, etc... ad infinitum. Which
seems to me to be strictly a background/behind-the-scenes format issue.
So, going back to my original point--
1- is there anyway to consistently get it to work one way, without having to
spend hours looking at why it doesn't?
2- Thus far the incongruencies that I've experienced with this routine is
aggravating at best, and downright ruinous at worst.
3- if no one here knows the answers that I seek, who can I speak with that
will get me the correct way to handle this?

SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim to
want using formulas like =1*Map!E4 in a different range of 27 rows by 1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go through and
find out why one equation works and the very next one does not work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going through
looking for small, inconspicuous reasons for its failure. I came looking to
learn more about this equation because it did once work, and it sped up my
work process by an undefinable amount of time. It's now taking 3 x's longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with possible
floating point rounding/truncation error) while leaving numeric values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text strings,
when the other column its comparing to could be being recognized as numeric
values, and as such it returns a false because the text string is not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case. VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric in one
cell, and a text string in the other column. I just want it to be recognized
as being the same, because regardless of the format/properties behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other. You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas you're
going to have to break down and learn this distinction. To repeat from
above, this behavior is unlikely to change. Your choices are either to
learn this and accommodate how Excel works or use something other than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one column, and
12345 in the other. Nor did I enter it as <12345, etc... ad infinitum. Which
seems to me to be strictly a background/behind-the-scenes format issue.

OK, but if one cell was formatted as Text while the other was formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.

Well, I'll tell you what Harlan, if you don't want to believe me, I guess
that's your own problem. But to satisfy your obvious ignorance, give me your
email-- one you feel safe with-- and I'll put together a sample worksheet
from a blank workbook, and email it to you. This way you'll have that "live
example" you mentioned.
I've got better things to do than be insulted by someone who obviously has
no understanding of what I'm trying to accomplish.
This was suppose to be a simple, straightforward question to what's
obviously not so simple a problem.
That MS is, or would be unwilling to resolve an issue that affects all of
their customers is what has cost them the "open source" people to begin with.
That you'd act as if I'd insulted your integrity by saying that the example
you'd given did not work tells me that you've taken this far too personally,
when it has/had absolutely nothing to do with you is equally disappointing.
I will however not let this go, and expect a more patient person than you to
deal with this issue.
There is nothing wrong with the dataset that is being used. The other
engineer that I'd initially mentioned in my first post to Roger and myself
have both opened completely blank workbooks to run our own tests on this and
have found glaring inconsistencies-- it will work with one row, but not the
next. It then may work with a few rows, and then not work with others.
He's tried using it on basketball, baseball, and other sports, and
experiecned the same exact issues. The help file in Excel said it's supposed
to work with upwards of 30 distinct--equally dimensioned-- arrays.

All we're seeking is a consistently acting "formula." That you'd pull a
semantics response is pathetic. Formulas, equations, and algorithms are all
the same thing-- a mathematical statement designed to elicit an answer. At
least have the decency to answer my question instead of playing language
games with me.
So, as I said, since you believe yourself to be infallible, send me an email
address and I'll get you a workbook with a sample dataset, and the
equations/formulas/algorithms.


"Harlan Grove" wrote:

SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim to
want using formulas like =1*Map!E4 in a different range of 27 rows by 1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go through and
find out why one equation works and the very next one does not work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going through
looking for small, inconspicuous reasons for its failure. I came looking to
learn more about this equation because it did once work, and it sped up my
work process by an undefinable amount of time. It's now taking 3 x's longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with possible
floating point rounding/truncation error) while leaving numeric values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text strings,
when the other column its comparing to could be being recognized as numeric
values, and as such it returns a false because the text string is not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case. VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric in one
cell, and a text string in the other column. I just want it to be recognized
as being the same, because regardless of the format/properties behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other. You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas you're
going to have to break down and learn this distinction. To repeat from
above, this behavior is unlikely to change. Your choices are either to
learn this and accommodate how Excel works or use something other than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one column, and
12345 in the other. Nor did I enter it as <12345, etc... ad infinitum. Which
seems to me to be strictly a background/behind-the-scenes format issue.

OK, but if one cell was formatted as Text while the other was formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.

Hi Steve

Calm down!!!

I posted a reply to you at 17:58 on 17/11/06 in which I tried to explain
the differences in data types and reasons why you were getting your
inconsistent results (to which you did not respond).
Harlan has more thoroughly and eloquently explained the issues and was
in no way insulting you, I don't see why you took it that way.
All we have been trying to do is help you. None of us are employees of
Microsoft, none of us get paid for trying to help.
It is of course your prerogative as to whether you wish to accept any
advice in respect to a problem which you have posted into the forum.

I have used Sumproduct reliably for many years in all sorts of
applications for myself and clients provided I have obeyed the simple
rules of ensuring that I am comparing similar data types. More expert
users than myself, like Harlan and many others who frequent these
newsgroups, have also used the function very successfully and reliably.

Like Harlan, I would also be sceptical of inconsistency in outcome,
where data types are consistent and many is the time that I have sworn
to myself that the program is behaving wrongly, only to find that it is
a mistake on my part. We all make them, and hopefully we all learn from
them.

As I suggested in my email, and Harlan in his, it may well have been
that the Reference numbers were copied and pasted in from another
application, and were therefore text values at source and became text
values at destination. Or, as I suggested in my posting, if the
reference numbers are greater than 15 digits, they would have to be
entered as text values (read my email again).

Leave them as Text, just format your cell A3 as Text and enter your
required lookup and all will be well. Consistency will be achieved on
both sides and your blood pressure may return to normal levels <bg.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Well, I'll tell you what Harlan, if you don't want to believe me, I
guess
that's your own problem. But to satisfy your obvious ignorance, give
me your
email-- one you feel safe with-- and I'll put together a sample
worksheet
from a blank workbook, and email it to you. This way you'll have that
"live
example" you mentioned.
I've got better things to do than be insulted by someone who obviously
has
no understanding of what I'm trying to accomplish.
This was suppose to be a simple, straightforward question to what's
obviously not so simple a problem.
That MS is, or would be unwilling to resolve an issue that affects all
of
their customers is what has cost them the "open source" people to
begin with.
That you'd act as if I'd insulted your integrity by saying that the
example
you'd given did not work tells me that you've taken this far too
personally,
when it has/had absolutely nothing to do with you is equally
disappointing.
I will however not let this go, and expect a more patient person than
you to
deal with this issue.
There is nothing wrong with the dataset that is being used. The other
engineer that I'd initially mentioned in my first post to Roger and
myself
have both opened completely blank workbooks to run our own tests on
this and
have found glaring inconsistencies-- it will work with one row, but
not the
next. It then may work with a few rows, and then not work with others.
He's tried using it on basketball, baseball, and other sports, and
experiecned the same exact issues. The help file in Excel said it's
supposed
to work with upwards of 30 distinct--equally dimensioned-- arrays.

All we're seeking is a consistently acting "formula." That you'd pull
a
semantics response is pathetic. Formulas, equations, and algorithms
are all
the same thing-- a mathematical statement designed to elicit an
answer. At
least have the decency to answer my question instead of playing
language
games with me.
So, as I said, since you believe yourself to be infallible, send me an
email
address and I'll get you a workbook with a sample dataset, and the
equations/formulas/algorithms.


"Harlan Grove" wrote:

SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does
not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim
to
want using formulas like =1*Map!E4 in a different range of 27 rows by
1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go
through and
find out why one equation works and the very next one does not
work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type
mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively
up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going
through
looking for small, inconspicuous reasons for its failure. I came
looking to
learn more about this equation because it did once work, and it sped
up my
work process by an undefinable amount of time. It's now taking 3 x's
longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or
numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with
possible
floating point rounding/truncation error) while leaving numeric
values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text
strings,
when the other column its comparing to could be being recognized as
numeric
values, and as such it returns a false because the text string is
not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case.
VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's
because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other
people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric
in one
cell, and a text string in the other column. I just want it to be
recognized
as being the same, because regardless of the format/properties
behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other.
You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas
you're
going to have to break down and learn this distinction. To repeat
from
above, this behavior is unlikely to change. Your choices are either
to
learn this and accommodate how Excel works or use something other
than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one
column, and
12345 in the other. Nor did I enter it as <12345, etc... ad
infinitum. Which
seems to me to be strictly a background/behind-the-scenes format
issue.

OK, but if one cell was formatted as Text while the other was
formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without
having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak
with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.

Roger,
My response in that manner was due solely to the fact he said he did not
believe me. I've got no tolerance or patience for people who call me a liar.
And quite frankly, I don't have time to lie about this. I'm already too busy
to waste that kind of time.
I know that I'm not doing something right. I know that whatever it is that I
am doing is not working. I would not be here if all I wanted to do was to
blame someone else for my Excel woes. I've been trying to get this routine to
work, consistently, for approx. 4 months now. I'd initially tried using sumif
instead, but it appears to not take more than one test grouping. However, it
did work consistently. I never had any problems with it-- of any kind. I'm
only here because I need to solve a problem.
Now, onto the issues.
I get that we're talking data types. I get that computers look at values
input into cells as datatypes, and put no initial property to that data other
than what we apply to it-- gigo. I further get that he believed that it was
something that I'd done to the data that would've caused it to resolve into
my stated problem.
However, I did nothing other than input data into existing worksheets, and
apply the "formula" to those datasets. I have made various modifications to
the sheets to determine why I'd get various outputs. But that was AFTER they
failed.
One of my colleagues, and myself have both taken blank, "unspoiled"
worksheets and tried the sumproduct formula. Each of us have gotten different
results, resulting in an unexplained set of inconsistent behaviours/results.
As mentioned in my last post to Harlan, I took a blank worksheet, and did a
sample dataset. I did not format anything.
I tried all of his "formulas" and only one worked. And that was the one
where both the source, and test ended with the &"". The one with the double
-- signs returned #value, as did the one with the single minus sign. The one
where the test set had the &"", and the source set did not have it did not
work. It returned a 0.00.
Ok, so you guys are saying that I either need to set the source, or the test
sets to a text string, or numeric type datatype.
The problem that I'm attempting to get around is that we have over 1000
workbooks stored on our server with various sources dating back to the late
1990's. When we get around to updating these, we have no idea where they
originated from beyond our office. When I get them, I go through and reset
all of the formatting to the most basic that I'm able to do, WITHOUT having
to go through each, and every cell to do so. I.e., I'll highlight all of the
worksheet tabs, and rightclick, left click "format cells" and set my font to
arial, set the font-size to 12, and for specific columns, set to number, or
text, etc... All common.
but when a column shows me (within the format cells window) that it's set to
general, or text, or numeric, and then acts differently, that adds to my work
load by far too much to be forced to look through each and every cell and
force or coerce the result.
It is this that I need, and am trying to overcome.
So, now, back to the beginning.
How can I resolve this problem.
1- it must act consistently- out of an office of 10 engineers who would have
access to these files, if I'm the only one who uses it, it's pretty much
useless.
2- If I'm always trying to look for reasons why it's not working, then I'm
wasting time, and money.
All I care about at this point is getting it to work-- period.
So, how can I do this.... that's all I want.
For the larger issues, I've already resolved. I.e., you've already confirmed
that my equation is correct.
From our discussions, and the like, it still goes back to some type of
formatting. One column is set to a text datatype, and the other is a numeric
datatype. My point is that I did not set those. All I'm doing is entering a
number in a column, and changing a name from one value to another. I don't go
in and reformat unless it's absolutely necessary-- it's a waste of time.

"Roger Govier" wrote:

Hi Steve

Calm down!!!

I posted a reply to you at 17:58 on 17/11/06 in which I tried to explain
the differences in data types and reasons why you were getting your
inconsistent results (to which you did not respond).
Harlan has more thoroughly and eloquently explained the issues and was
in no way insulting you, I don't see why you took it that way.
All we have been trying to do is help you. None of us are employees of
Microsoft, none of us get paid for trying to help.
It is of course your prerogative as to whether you wish to accept any
advice in respect to a problem which you have posted into the forum.

I have used Sumproduct reliably for many years in all sorts of
applications for myself and clients provided I have obeyed the simple
rules of ensuring that I am comparing similar data types. More expert
users than myself, like Harlan and many others who frequent these
newsgroups, have also used the function very successfully and reliably.

Like Harlan, I would also be sceptical of inconsistency in outcome,
where data types are consistent and many is the time that I have sworn
to myself that the program is behaving wrongly, only to find that it is
a mistake on my part. We all make them, and hopefully we all learn from
them.

As I suggested in my email, and Harlan in his, it may well have been
that the Reference numbers were copied and pasted in from another
application, and were therefore text values at source and became text
values at destination. Or, as I suggested in my posting, if the
reference numbers are greater than 15 digits, they would have to be
entered as text values (read my email again).

Leave them as Text, just format your cell A3 as Text and enter your
required lookup and all will be well. Consistency will be achieved on
both sides and your blood pressure may return to normal levels <bg.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Well, I'll tell you what Harlan, if you don't want to believe me, I
guess
that's your own problem. But to satisfy your obvious ignorance, give
me your
email-- one you feel safe with-- and I'll put together a sample
worksheet
from a blank workbook, and email it to you. This way you'll have that
"live
example" you mentioned.
I've got better things to do than be insulted by someone who obviously
has
no understanding of what I'm trying to accomplish.
This was suppose to be a simple, straightforward question to what's
obviously not so simple a problem.
That MS is, or would be unwilling to resolve an issue that affects all
of
their customers is what has cost them the "open source" people to
begin with.
That you'd act as if I'd insulted your integrity by saying that the
example
you'd given did not work tells me that you've taken this far too
personally,
when it has/had absolutely nothing to do with you is equally
disappointing.
I will however not let this go, and expect a more patient person than
you to
deal with this issue.
There is nothing wrong with the dataset that is being used. The other
engineer that I'd initially mentioned in my first post to Roger and
myself
have both opened completely blank workbooks to run our own tests on
this and
have found glaring inconsistencies-- it will work with one row, but
not the
next. It then may work with a few rows, and then not work with others.
He's tried using it on basketball, baseball, and other sports, and
experiecned the same exact issues. The help file in Excel said it's
supposed
to work with upwards of 30 distinct--equally dimensioned-- arrays.

All we're seeking is a consistently acting "formula." That you'd pull
a
semantics response is pathetic. Formulas, equations, and algorithms
are all
the same thing-- a mathematical statement designed to elicit an
answer. At
least have the decency to answer my question instead of playing
language
games with me.
So, as I said, since you believe yourself to be infallible, send me an
email
address and I'll get you a workbook with a sample dataset, and the
equations/formulas/algorithms.


"Harlan Grove" wrote:

SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does
not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim
to
want using formulas like =1*Map!E4 in a different range of 27 rows by
1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go
through and
find out why one equation works and the very next one does not
work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type
mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively
up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going
through
looking for small, inconspicuous reasons for its failure. I came
looking to
learn more about this equation because it did once work, and it sped
up my
work process by an undefinable amount of time. It's now taking 3 x's
longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or
numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with
possible
floating point rounding/truncation error) while leaving numeric
values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text
strings,
when the other column its comparing to could be being recognized as
numeric
values, and as such it returns a false because the text string is
not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case.
VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's
because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other
people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric
in one
cell, and a text string in the other column. I just want it to be
recognized
as being the same, because regardless of the format/properties
behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other.
You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas
you're
going to have to break down and learn this distinction. To repeat
from
above, this behavior is unlikely to change. Your choices are either
to
learn this and accommodate how Excel works or use something other
than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one
column, and
12345 in the other. Nor did I enter it as <12345, etc... ad
infinitum. Which
seems to me to be strictly a background/behind-the-scenes format
issue.

OK, but if one cell was formatted as Text while the other was
formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without
having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak
with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.

Hi all,

I want to share but I don't want to be yelled at if I have said something wrong. I haven't analyzed the OP's formula. I am here to make a comment on the following.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
......... It's entirely and exclusively up to YOU to ensure that you're comparing text to text or numbers to numbers. <<

The above sums up my latest discovery of how crucial it is to match data type when I write SUMPRODUCT formula. SUMPRODUCT is not as *forgiving* as other functions, say COUNTIF.

Scenario 1

If I format a brand new blank cell as TEXT and key in a number say 2006, the format is text. ISTEXT( ) returns TRUE.

Scenario 2

If I key in 2006 to a brand new blank cell which has a default format of general and then format the cell as TEXT, the format is NOT text. ISTEXT( ) returns FALSE.

When I write my SUMPRODUCT formula, the syntax for =2006 for scenario 1 must be in quotes i.e. ="2006" because I want to compare text to text. For scenario 2, I must key in =2006 (without quotes) because I want to compare number to number.

COUNTIF doesn't care if I have quotes or not for both scenarios and returns the proper result. It is much more "forgiving" than SUMPRODUCT. Harlan and Roger, please correct me if I am wrong.

As much as I like SUMPRODUCT, I have realized that it can be demanding and I must be careful in matching my data type i.e. "comparing text to text or numbers to numbers."

Epinn

Be kinder than necessary, for everyone you meet is fighting some kind of battle.
Attitude is everything.

"Harlan Grove" wrote in message oups.com...
SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim to
want using formulas like =1*Map!E4 in a different range of 27 rows by 1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go through and
find out why one equation works and the very next one does not work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going through
looking for small, inconspicuous reasons for its failure. I came looking to
learn more about this equation because it did once work, and it sped up my
work process by an undefinable amount of time. It's now taking 3 x's longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with possible
floating point rounding/truncation error) while leaving numeric values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text strings,
when the other column its comparing to could be being recognized as numeric
values, and as such it returns a false because the text string is not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case. VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric in one
cell, and a text string in the other column. I just want it to be recognized
as being the same, because regardless of the format/properties behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other. You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas you're
going to have to break down and learn this distinction. To repeat from
above, this behavior is unlikely to change. Your choices are either to
learn this and accommodate how Excel works or use something other than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one column, and
12345 in the other. Nor did I enter it as <12345, etc... ad infinitum. Which
seems to me to be strictly a background/behind-the-scenes format issue.

OK, but if one cell was formatted as Text while the other was formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.

I want to share with all of you my experiment done before I even came to this thread.

Let's say in January scenario 2 is happening i.e. a cell is formatted to text *after* the value is keyed in.

B1: 2006 <--- ISTEXT( ) returns FALSE.
B2: apple
B3: 1

=SUMPRODUCT(--($B$1:$B$3=2006)) returns 1
=SUMPRODUCT(--($B$1:$B$3="2006")) returns 0

In February, someone rekeys 2006 to B1 and presses enter.

B1: 2006 <--- ISTEXT ( ) returns TRUE.

=SUMPRODUCT(--($B$1:$B$3=2006)) returns 0
=SUMPRODUCT(--($B$1:$B$3="2006")) returns 1

Please note the results of the two formulae - a complete reversal of those for January. This is because the data type has been changed from non-text to text.

So, I can see inconsistency in the results generated by the same formula on the same data/value but *different data type*. Yes, you don't even have to do a format and the data type gets changed. It was explained to me that "format as Text only asserts once data has been entered."

Bottom line is data type change affects the result of the formula.

Could something similar be happening in the OP's office?

Epinn

"Epinn" wrote in message ...
Hi all,

I want to share but I don't want to be yelled at if I have said something wrong. I haven't analyzed the OP's formula. I am here to make a comment on the following.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
......... It's entirely and exclusively up to YOU to ensure that you're comparing text to text or numbers to numbers. <<

The above sums up my latest discovery of how crucial it is to match data type when I write SUMPRODUCT formula. SUMPRODUCT is not as *forgiving* as other functions, say COUNTIF.

Scenario 1

If I format a brand new blank cell as TEXT and key in a number say 2006, the format is text. ISTEXT( ) returns TRUE.

Scenario 2

If I key in 2006 to a brand new blank cell which has a default format of general and then format the cell as TEXT, the format is NOT text. ISTEXT( ) returns FALSE.

When I write my SUMPRODUCT formula, the syntax for =2006 for scenario 1 must be in quotes i.e. ="2006" because I want to compare text to text. For scenario 2, I must key in =2006 (without quotes) because I want to compare number to number.

COUNTIF doesn't care if I have quotes or not for both scenarios and returns the proper result. It is much more "forgiving" than SUMPRODUCT. Harlan and Roger, please correct me if I am wrong.

As much as I like SUMPRODUCT, I have realized that it can be demanding and I must be careful in matching my data type i.e. "comparing text to text or numbers to numbers."

Epinn

Be kinder than necessary, for everyone you meet is fighting some kind of battle.
Attitude is everything.

"Harlan Grove" wrote in message oups.com...
SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim to
want using formulas like =1*Map!E4 in a different range of 27 rows by 1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go through and
find out why one equation works and the very next one does not work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going through
looking for small, inconspicuous reasons for its failure. I came looking to
learn more about this equation because it did once work, and it sped up my
work process by an undefinable amount of time. It's now taking 3 x's longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with possible
floating point rounding/truncation error) while leaving numeric values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text strings,
when the other column its comparing to could be being recognized as numeric
values, and as such it returns a false because the text string is not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case. VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric in one
cell, and a text string in the other column. I just want it to be recognized
as being the same, because regardless of the format/properties behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other. You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas you're
going to have to break down and learn this distinction. To repeat from
above, this behavior is unlikely to change. Your choices are either to
learn this and accommodate how Excel works or use something other than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one column, and
12345 in the other. Nor did I enter it as <12345, etc... ad infinitum. Which
seems to me to be strictly a background/behind-the-scenes format issue.

OK, but if one cell was formatted as Text while the other was formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.

SteveDB1 wrote...
Well, I'll tell you what Harlan, if you don't want to believe me, I guess
that's your own problem. . . .

Not exactly a problem. And I don't believe you're consciously lying. I
believe you don't know what you're doing and you don't know how to
report your results accurately. In other words, incompetence, not
dishonesty. I hope that makes you feel better.

. . . But to satisfy your obvious ignorance, . . .

Well, one of us is ignorant.

. . . give me your
email-- one you feel safe with-- and I'll put together a sample worksheet
from a blank workbook, and email it to you. This way you'll have that "live
example" you mentioned.

Use Google Groups and you can see my e-mail address. You'd just need to
click on the ... link in my e-mail address then follow the directions.

I've got better things to do than be insulted by someone who obviously has
no understanding of what I'm trying to accomplish.
....

You believe you're the first person posting here who's had problems
with data type mismatches in SUMPRODUCT formulas, do you?

I know what you're trying to accomplish, and I also know why what
you've tried doesn't work, and I have a shrewd guess why you can't
accept the answer unless someone rubs your face in it, and even that's
unlikely to work.

That MS is, or would be unwilling to resolve an issue that affects all of
their customers . . .

It only affects the pigheaded one who refuse to learn how Excel works.

That you'd act as if I'd insulted your integrity by saying that the example
you'd given did not work tells me that you've taken this far too personally,
when it has/had absolutely nothing to do with you is equally disappointing.

What example did I give? =(12345="12345") returning FALSE? It does. If
it returns anything else on your system, then perhaps your copy of
Excel is corrupted. That's the only example I gave. Or did you mean
formula?

I will however not let this go, and expect a more patient person than you to
deal with this issue.

Yeah, so many have done so already, haven't they? And having seen that
you've been given the usual (and correct) answer to this problem but
that you refuse to accept it, they're just dying to try a warner &
fuzzier approach.

There is nothing wrong with the dataset that is being used. The other
engineer that I'd initially mentioned in my first post to Roger and myself
have both opened completely blank workbooks to run our own tests on this and
have found glaring inconsistencies-- it will work with one row, but not the
next. It then may work with a few rows, and then not work with others.
....

OK, so there are two of you who have no clue.

All we're seeking is a consistently acting "formula." That you'd pull a
semantics response is pathetic. Formulas, equations, and algorithms are all
the same thing-- a mathematical statement designed to elicit an answer. At
least have the decency to answer my question instead of playing language
games with me.

Terminology is important, at least to people who understand it.

Formulas, equations and algorithms are NOT the same thing. Formulas, in
the spreadsheet sense, are like assignment statements in programming
languages. As for algorithms, how would one implement binary search as
a series of equations?

So, as I said, since you believe yourself to be infallible, send me an email
address and I'll get you a workbook with a sample dataset, and the
equations/formulas/algorithms.

Figure out how to use Google Groups, and you'll have my e-mail address.
If you're an engineer, you should be able to accomplish that.

If you do the same experiment using COUNTIF instead of SUMPRODUCT, the four formulae for both January and February will return 1 regardless of data type.

The OP mentions that he finds consistency with SUMIF but not SUMPRODUCT. I am not surprised after these experiments. As I said previously, SUMPRODUCT is not as forgiving and one must handle data type very carefully.

I am glad that I have discovered all this at this very early stage of learning Excel.

Epinn

"Epinn" wrote in message ...
I want to share with all of you my experiment done before I even came to this thread.

Let's say in January scenario 2 is happening i.e. a cell is formatted to text *after* the value is keyed in.

B1: 2006 <--- ISTEXT( ) returns FALSE.
B2: apple
B3: 1

=SUMPRODUCT(--($B$1:$B$3=2006)) returns 1
=SUMPRODUCT(--($B$1:$B$3="2006")) returns 0

In February, someone rekeys 2006 to B1 and presses enter.

B1: 2006 <--- ISTEXT ( ) returns TRUE.

=SUMPRODUCT(--($B$1:$B$3=2006)) returns 0
=SUMPRODUCT(--($B$1:$B$3="2006")) returns 1

Please note the results of the two formulae - a complete reversal of those for January. This is because the data type has been changed from non-text to text.

So, I can see inconsistency in the results generated by the same formula on the same data/value but *different data type*. Yes, you don't even have to do a format and the data type gets changed. It was explained to me that "format as Text only asserts once data has been entered."

Bottom line is data type change affects the result of the formula.

Could something similar be happening in the OP's office?

Epinn

"Epinn" wrote in message ...
Hi all,

I want to share but I don't want to be yelled at if I have said something wrong. I haven't analyzed the OP's formula. I am here to make a comment on the following.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
......... It's entirely and exclusively up to YOU to ensure that you're comparing text to text or numbers to numbers. <<

The above sums up my latest discovery of how crucial it is to match data type when I write SUMPRODUCT formula. SUMPRODUCT is not as *forgiving* as other functions, say COUNTIF.

Scenario 1

If I format a brand new blank cell as TEXT and key in a number say 2006, the format is text. ISTEXT( ) returns TRUE.

Scenario 2

If I key in 2006 to a brand new blank cell which has a default format of general and then format the cell as TEXT, the format is NOT text. ISTEXT( ) returns FALSE.

When I write my SUMPRODUCT formula, the syntax for =2006 for scenario 1 must be in quotes i.e. ="2006" because I want to compare text to text. For scenario 2, I must key in =2006 (without quotes) because I want to compare number to number.

COUNTIF doesn't care if I have quotes or not for both scenarios and returns the proper result. It is much more "forgiving" than SUMPRODUCT. Harlan and Roger, please correct me if I am wrong.

As much as I like SUMPRODUCT, I have realized that it can be demanding and I must be careful in matching my data type i.e. "comparing text to text or numbers to numbers."

Epinn

Be kinder than necessary, for everyone you meet is fighting some kind of battle.
Attitude is everything.

"Harlan Grove" wrote in message oups.com...
SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim to
want using formulas like =1*Map!E4 in a different range of 27 rows by 1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go through and
find out why one equation works and the very next one does not work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going through
looking for small, inconspicuous reasons for its failure. I came looking to
learn more about this equation because it did once work, and it sped up my
work process by an undefinable amount of time. It's now taking 3 x's longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with possible
floating point rounding/truncation error) while leaving numeric values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text strings,
when the other column its comparing to could be being recognized as numeric
values, and as such it returns a false because the text string is not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case. VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric in one
cell, and a text string in the other column. I just want it to be recognized
as being the same, because regardless of the format/properties behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other. You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas you're
going to have to break down and learn this distinction. To repeat from
above, this behavior is unlikely to change. Your choices are either to
learn this and accommodate how Excel works or use something other than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one column, and
12345 in the other. Nor did I enter it as <12345, etc... ad infinitum. Which
seems to me to be strictly a background/behind-the-scenes format issue.

OK, but if one cell was formatted as Text while the other was formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.

Hi Steve

I don't think anybody was calling anybody a liar. People are often
mistaken when they make an assertion which someone else believes to be
incorrect i.e. the implication is mistaken, not a liar. Still, enough of
this and every one getting hot under the collar as it serves no purpose.

In the context of what you now describe, with large numbers of files
where there may have been inconsistency of data entry in the past, the
problem as I see it is twofold.
1. How do you ensure that all future data entry is consistent
2. What is the ideal construct of your Sumproduct expressions to deal
with potentially different data constructs.

If you would like to mail me a copy of a set of past data that works,
another one of a set of past data that doesn't work and a copy of one of
your new blank sheets where you say you can create a problem, I would be
happy to take a look and make a suggestion - albeit that it will only
encapsulate parts of suggestions already made by either Harlan or
myself.

If you want to send the files direct, remove NOSPAM from my address.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Roger,
My response in that manner was due solely to the fact he said he did
not
believe me. I've got no tolerance or patience for people who call me a
liar.
And quite frankly, I don't have time to lie about this. I'm already
too busy
to waste that kind of time.
I know that I'm not doing something right. I know that whatever it is
that I
am doing is not working. I would not be here if all I wanted to do was
to
blame someone else for my Excel woes. I've been trying to get this
routine to
work, consistently, for approx. 4 months now. I'd initially tried
using sumif
instead, but it appears to not take more than one test grouping.
However, it
did work consistently. I never had any problems with it-- of any kind.
I'm
only here because I need to solve a problem.
Now, onto the issues.
I get that we're talking data types. I get that computers look at
values
input into cells as datatypes, and put no initial property to that
data other
than what we apply to it-- gigo. I further get that he believed that
it was
something that I'd done to the data that would've caused it to resolve
into
my stated problem.
However, I did nothing other than input data into existing worksheets,
and
apply the "formula" to those datasets. I have made various
modifications to
the sheets to determine why I'd get various outputs. But that was
AFTER they
failed.
One of my colleagues, and myself have both taken blank, "unspoiled"
worksheets and tried the sumproduct formula. Each of us have gotten
different
results, resulting in an unexplained set of inconsistent
behaviours/results.
As mentioned in my last post to Harlan, I took a blank worksheet, and
did a
sample dataset. I did not format anything.
I tried all of his "formulas" and only one worked. And that was the
one
where both the source, and test ended with the &"". The one with the
double
-- signs returned #value, as did the one with the single minus sign.
The one
where the test set had the &"", and the source set did not have it did
not
work. It returned a 0.00.
Ok, so you guys are saying that I either need to set the source, or
the test
sets to a text string, or numeric type datatype.
The problem that I'm attempting to get around is that we have over
1000
workbooks stored on our server with various sources dating back to the
late
1990's. When we get around to updating these, we have no idea where
they
originated from beyond our office. When I get them, I go through and
reset
all of the formatting to the most basic that I'm able to do, WITHOUT
having
to go through each, and every cell to do so. I.e., I'll highlight all
of the
worksheet tabs, and rightclick, left click "format cells" and set my
font to
arial, set the font-size to 12, and for specific columns, set to
number, or
text, etc... All common.
but when a column shows me (within the format cells window) that it's
set to
general, or text, or numeric, and then acts differently, that adds to
my work
load by far too much to be forced to look through each and every cell
and
force or coerce the result.
It is this that I need, and am trying to overcome.
So, now, back to the beginning.
How can I resolve this problem.
1- it must act consistently- out of an office of 10 engineers who
would have
access to these files, if I'm the only one who uses it, it's pretty
much
useless.
2- If I'm always trying to look for reasons why it's not working, then
I'm
wasting time, and money.
All I care about at this point is getting it to work-- period.
So, how can I do this.... that's all I want.
For the larger issues, I've already resolved. I.e., you've already
confirmed
that my equation is correct.
From our discussions, and the like, it still goes back to some type of
formatting. One column is set to a text datatype, and the other is a
numeric
datatype. My point is that I did not set those. All I'm doing is
entering a
number in a column, and changing a name from one value to another. I
don't go
in and reformat unless it's absolutely necessary-- it's a waste of
time.

"Roger Govier" wrote:

Hi Steve

Calm down!!!

I posted a reply to you at 17:58 on 17/11/06 in which I tried to
explain
the differences in data types and reasons why you were getting your
inconsistent results (to which you did not respond).
Harlan has more thoroughly and eloquently explained the issues and
was
in no way insulting you, I don't see why you took it that way.
All we have been trying to do is help you. None of us are employees
of
Microsoft, none of us get paid for trying to help.
It is of course your prerogative as to whether you wish to accept any
advice in respect to a problem which you have posted into the forum.

I have used Sumproduct reliably for many years in all sorts of
applications for myself and clients provided I have obeyed the simple
rules of ensuring that I am comparing similar data types. More expert
users than myself, like Harlan and many others who frequent these
newsgroups, have also used the function very successfully and
reliably.

Like Harlan, I would also be sceptical of inconsistency in outcome,
where data types are consistent and many is the time that I have
sworn
to myself that the program is behaving wrongly, only to find that it
is
a mistake on my part. We all make them, and hopefully we all learn
from
them.

As I suggested in my email, and Harlan in his, it may well have been
that the Reference numbers were copied and pasted in from another
application, and were therefore text values at source and became text
values at destination. Or, as I suggested in my posting, if the
reference numbers are greater than 15 digits, they would have to be
entered as text values (read my email again).

Leave them as Text, just format your cell A3 as Text and enter your
required lookup and all will be well. Consistency will be achieved on
both sides and your blood pressure may return to normal levels <bg.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...
Well, I'll tell you what Harlan, if you don't want to believe me, I
guess
that's your own problem. But to satisfy your obvious ignorance,
give
me your
email-- one you feel safe with-- and I'll put together a sample
worksheet
from a blank workbook, and email it to you. This way you'll have
that
"live
example" you mentioned.
I've got better things to do than be insulted by someone who
obviously
has
no understanding of what I'm trying to accomplish.
This was suppose to be a simple, straightforward question to what's
obviously not so simple a problem.
That MS is, or would be unwilling to resolve an issue that affects
all
of
their customers is what has cost them the "open source" people to
begin with.
That you'd act as if I'd insulted your integrity by saying that the
example
you'd given did not work tells me that you've taken this far too
personally,
when it has/had absolutely nothing to do with you is equally
disappointing.
I will however not let this go, and expect a more patient person
than
you to
deal with this issue.
There is nothing wrong with the dataset that is being used. The
other
engineer that I'd initially mentioned in my first post to Roger and
myself
have both opened completely blank workbooks to run our own tests on
this and
have found glaring inconsistencies-- it will work with one row, but
not the
next. It then may work with a few rows, and then not work with
others.
He's tried using it on basketball, baseball, and other sports, and
experiecned the same exact issues. The help file in Excel said it's
supposed
to work with upwards of 30 distinct--equally dimensioned-- arrays.

All we're seeking is a consistently acting "formula." That you'd
pull
a
semantics response is pathetic. Formulas, equations, and algorithms
are all
the same thing-- a mathematical statement designed to elicit an
answer. At
least have the decency to answer my question instead of playing
language
games with me.
So, as I said, since you believe yourself to be infallible, send me
an
email
address and I'll get you a workbook with a sample dataset, and the
equations/formulas/algorithms.


"Harlan Grove" wrote:

SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one
does
not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you
claim
to
want using formulas like =1*Map!E4 in a different range of 27 rows
by
1
column and using that range rather than Map!E4:E30 in your
SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30
in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and
I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that
case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go
through and
find out why one equation works and the very next one does not
work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type
mismatches
between your ranges Map!E4:E30 and A3. It's entirely and
exclusively
up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is
not
consistently working, and I'm spending far too much time going
through
looking for small, inconspicuous reasons for its failure. I came
looking to
learn more about this equation because it did once work, and it
sped
up my
work process by an undefinable amount of time. It's now taking 3
x's
longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the
most
likely answer is that there are problems with YOUR data. To
repeat,
it's up to YOU to ensure that you're comparing text to text or
numbers
to numbers. There are ways to do that: --x is guaranteed to
convert
text representations of numbers to their numeric values (with
possible
floating point rounding/truncation error) while leaving numeric
values
unchanged, and x&"" is guaranteed to convert numeric values to
their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already
shown
how to handle those.

You're saying that one column could be being recognized as text
strings,
when the other column its comparing to could be being recognized
as
numeric
values, and as such it returns a false because the text string is
not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include
numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another
data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case.
VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's
because
VBA and those other languages apply implicit type conversion even
to
operands of comparison operators. Excel doesn't. Whether it should
or
not is a different matter, and purely academic because Microsoft
is
extremely unlikely to change this behavior lest it break other
people's
existing formulas.

I don't care what the value is. I don't care that 12345 is
numeric
in one
cell, and a text string in the other column. I just want it to be
recognized
as being the same, because regardless of the format/properties
behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the
other.
You
may not appreciate the difference, and it's clear you don't want
to
have to do so, but if you want to create reliable Excel formulas
you're
going to have to break down and learn this distinction. To repeat
from
above, this behavior is unlikely to change. Your choices are
either
to
learn this and accommodate how Excel works or use something other
than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one
column, and
12345 in the other. Nor did I enter it as <12345, etc... ad
infinitum. Which
seems to me to be strictly a background/behind-the-scenes format
issue.

OK, but if one cell was formatted as Text while the other was
formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of
permit
numbers from some other system. If so, it's likely that other
system
stored the permit numbers as text, and Excel is respecting that
other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way,
without
having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak
with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a
different
answer than I have. To repeat, this is just how Excel works, and
it's
unlikely to change.

Hi Harlan,



I want to thank you for being detailed and taking the time to explain SUMPRODUCT, data types, coercion etc. I learned a lot reading your posts in this thread. It has been educational. I'll take the following advice to heart.



--x is guaranteed to convert text representations of numbers to their numeric values (with possible floating point rounding/truncation error) while leaving numeric values unchanged, and x&"" is guaranteed to convert numeric values to their text representations while leaving text as-is. <<



In my opinion, the fact that you are meticulous/precise on terminology is a reflection of your expertise. I always believe that programmers do well in Excel because writing a formula is like writing a line of code in a program. A sense of perfection is an important quality of a good programmer.



I also want to thank you for explaining #N/A (wildcard) to me the other day.



Last but not least, I don't think you have insulted anybody.



I am going to go over your posts again and if I have any questions, I'll start my own thread. If I do, I hope you have time to drop by.



We are lucky to have you around sharing your expertise and *generosity*. Please don't give up on us.



I hope you still come to this thread and can read this.



Epinn


"Harlan Grove" wrote in message oups.com...
SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim to
want using formulas like =1*Map!E4 in a different range of 27 rows by 1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go through and
find out why one equation works and the very next one does not work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going through
looking for small, inconspicuous reasons for its failure. I came looking to
learn more about this equation because it did once work, and it sped up my
work process by an undefinable amount of time. It's now taking 3 x's longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with possible
floating point rounding/truncation error) while leaving numeric values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text strings,
when the other column its comparing to could be being recognized as numeric
values, and as such it returns a false because the text string is not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case. VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric in one
cell, and a text string in the other column. I just want it to be recognized
as being the same, because regardless of the format/properties behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other. You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas you're
going to have to break down and learn this distinction. To repeat from
above, this behavior is unlikely to change. Your choices are either to
learn this and accommodate how Excel works or use something other than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one column, and
12345 in the other. Nor did I enter it as <12345, etc... ad infinitum. Which
seems to me to be strictly a background/behind-the-scenes format issue.

OK, but if one cell was formatted as Text while the other was formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without having to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.

Last but not least, I don't think you have insulted anybody.
Harlan never insults anybody. He just informs in a "matter of fact" way.<G

--
Don Guillett
SalesAid Software

"Epinn" wrote in message
...
Hi Harlan,



I want to thank you for being detailed and taking the time to explain
SUMPRODUCT, data types, coercion etc. I learned a lot reading your posts in
this thread. It has been educational. I'll take the following advice to
heart.



--x is guaranteed to convert text representations of numbers to their
numeric values (with possible floating point rounding/truncation error)
while leaving numeric values unchanged, and x&"" is guaranteed to convert
numeric values to their text representations while leaving text as-is.
<<



In my opinion, the fact that you are meticulous/precise on terminology is a
reflection of your expertise. I always believe that programmers do well in
Excel because writing a formula is like writing a line of code in a program.
A sense of perfection is an important quality of a good programmer.



I also want to thank you for explaining #N/A (wildcard) to me the other day.



Last but not least, I don't think you have insulted anybody.



I am going to go over your posts again and if I have any questions, I'll
start my own thread. If I do, I hope you have time to drop by.



We are lucky to have you around sharing your expertise and *generosity*.
Please don't give up on us.



I hope you still come to this thread and can read this.



Epinn


"Harlan Grove" wrote in message
oups.com...
SteveDB1 wrote...
Ok, Harlan,
I tried the last two equations you mentioned, and the first one does not
work. It only returns a zero.
(--Map!$E$4:$E$30=$A3)

or

(Map!$E$4:$E$30=$A3&"")

Simply put, I don't believe you. If you can get the result you claim to
want using formulas like =1*Map!E4 in a different range of 27 rows by 1
column and using that range rather than Map!E4:E30 in your SUMPRODUCT
formula, then Excel would give the same result using --Map!E4:E30 in
the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm
going to be skeptical about claims otherwise until I see a live
example.

Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case
use

(Map!$E$4:$E$30=--$A3)

or

(Map!$E$4:$E$30&""=$A3)

But to be as generic as possible, use

(-Map!$E$4:$E$30=-$A3)

or

(Map!$E$4:$E$30&""=$A3&"")

So, all of this still brings me back to the point of-- how do I,
consistently, constantly, get it to work, without having to go through and
find out why one equation works and the very next one does not work.

There's NOTHING wrong with Excel's -- double operator or &"" or
SUMPRODUCT. The problem lies entirely in probable data type mismatches
between your ranges Map!E4:E30 and A3. It's entirely and exclusively up
to YOU to ensure that you're comparing text to text or numbers to
numbers.

My whole point for coming in here to ask is that the equation is not
consistently working, and I'm spending far too much time going through
looking for small, inconspicuous reasons for its failure. I came looking to
learn more about this equation because it did once work, and it sped up my
work process by an undefinable amount of time. It's now taking 3 x's longer
to figure out why it's not working.

It's not an 'equation'. It's a formula.

If your formulas appear to behave inconsistently, far & away the most
likely answer is that there are problems with YOUR data. To repeat,
it's up to YOU to ensure that you're comparing text to text or numbers
to numbers. There are ways to do that: --x is guaranteed to convert
text representations of numbers to their numeric values (with possible
floating point rounding/truncation error) while leaving numeric values
unchanged, and x&"" is guaranteed to convert numeric values to their
text representations while leaving text as-is. You could also have
trailing nonbreaking HTML spaces, and Roger Govier has already shown
how to handle those.

You're saying that one column could be being recognized as text strings,
when the other column its comparing to could be being recognized as numeric
values, and as such it returns a false because the text string is not
recognized the same as a numeric value.

Correct.

This then takes me back to the point of WHY?

Because that's how Excel works. Available data types include numbers
(usually double precision floating point), text, boolean and error
values. A value in one data type NEVER equals a value in another data
type even if they share an identical text representation.

There are some programming languages in which this isn't the case. VBA,
for instance, in which 1234# = "1234" returns TRUE. But that's because
VBA and those other languages apply implicit type conversion even to
operands of comparison operators. Excel doesn't. Whether it should or
not is a different matter, and purely academic because Microsoft is
extremely unlikely to change this behavior lest it break other people's
existing formulas.

I don't care what the value is. I don't care that 12345 is numeric in one
cell, and a text string in the other column. I just want it to be
recognized
as being the same, because regardless of the format/properties behind each,
it is still just 12345.

There you're wrong. It's text in one cell and numeric in the other. You
may not appreciate the difference, and it's clear you don't want to
have to do so, but if you want to create reliable Excel formulas you're
going to have to break down and learn this distinction. To repeat from
above, this behavior is unlikely to change. Your choices are either to
learn this and accommodate how Excel works or use something other than
Excel.

I entered it as JUST 12345. I did not enter it as "12345" in one column,
and
12345 in the other. Nor did I enter it as <12345, etc... ad infinitum.
Which
seems to me to be strictly a background/behind-the-scenes format issue.

OK, but if one cell was formatted as Text while the other was formatted
as General, then the first will be STORED as text and the other as
numeric.

At this point I have to say I suspect you imported the list of permit
numbers from some other system. If so, it's likely that other system
stored the permit numbers as text, and Excel is respecting that other
system's data types.

So, going back to my original point--
1- is there anyway to consistently get it to work one way, without having
to
spend hours looking at why it doesn't?
....

Only by always coercing both sides of your = or < comparisons to
numeric or text.

3- if no one here knows the answers that I seek, who can I speak with that
will get me the correct way to handle this?

No one who knows more about Excel that I do will give you a different
answer than I have. To repeat, this is just how Excel works, and it's
unlikely to change.

Hi all.
After intense discussion, and "debate" I'll consider this post answered for
now.
I have in fact tried all of Harlan's, and Roger's recommended solutions, and
have come up with what appears to be a viable means of solving the problem.
And as such has already been implimented in updating workbooks.
However, one of my colleagues has encountered an issue that I've not
encountered, and in fact, it has never been an issue for me.
He's found that all works except when he uses a formula in the final source
column-- i.e., the last "array" of the sumproduct, in our case:
=sumproduct ((...)*(...)*(Map!$C$3:$C$n))
Where 'n' is the end of the row range, and the formula that I'm referencing
in the C column is of the general form- (=B2*x/y)-- ignore the paren's; x,
and y are some predetermined values-- you can pick any arbitrary numbers.
I quite frankly can't think of any reason why it would not work, as mine has
always worked for this portion, and never once been an issue. My issues, it
turns out were cross-datatypes that were returned as false, when the values
represented should've returned true.
So, again-- thank you for everyone's assistance.
Hope all have a great T-day-- for those on this side of the "pond". For
those overseas... enjoy the rest of your week.
See you all next time I need help.

"SteveDB1" wrote:

Hi folks.
I use sumproduct to sum columns of values after testing against 2 criteria.
At first it was working great, and then it stopped working once one cell
that I was testing against had a value in it. It's actually become quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and cell b
would be the values that I'm looking for; and the Col&RowRangeFinal would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone so far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors, or #ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd hate to be
forced to use something else to solve my reasons for using it to begin with.

Hi Steve

Glad to hear that the explanations and formulae I sent you back having
seen your workbook solved your problems.

You don't say what problems you are now getting. What is the error that
is being returned?
Are you talking about your Acre-feet column like column D in the
workbook you sent me, with formulae like =C2*200/50 ?
On your workbook, that was accurately being calculated in column J of
sheet Sum.

If it is not working in another workbook, then there must be something
wrong with one or more of the values in column B, giving rise to an
error in the calculated result for column C.
It will not be an error with Sumproduct, but an error with the source
data that needs correcting.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...

Hi all.
After intense discussion, and "debate" I'll consider this post
answered for
now.
I have in fact tried all of Harlan's, and Roger's recommended
solutions, and
have come up with what appears to be a viable means of solving the
problem.
And as such has already been implimented in updating workbooks.
However, one of my colleagues has encountered an issue that I've not
encountered, and in fact, it has never been an issue for me.
He's found that all works except when he uses a formula in the final
source
column-- i.e., the last "array" of the sumproduct, in our case:
=sumproduct ((...)*(...)*(Map!$C$3:$C$n))
Where 'n' is the end of the row range, and the formula that I'm
referencing
in the C column is of the general form- (=B2*x/y)-- ignore the
paren's; x,
and y are some predetermined values-- you can pick any arbitrary
numbers.
I quite frankly can't think of any reason why it would not work, as
mine has
always worked for this portion, and never once been an issue. My
issues, it
turns out were cross-datatypes that were returned as false, when the
values
represented should've returned true.
So, again-- thank you for everyone's assistance.
Hope all have a great T-day-- for those on this side of the "pond".
For
those overseas... enjoy the rest of your week.
See you all next time I need help.

"SteveDB1" wrote:

Hi folks.
I use sumproduct to sum columns of values after testing against 2
criteria.
At first it was working great, and then it stopped working once one
cell
that I was testing against had a value in it. It's actually become
quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and
cell b
would be the values that I'm looking for; and the Col&RowRangeFinal
would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone
so far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be
really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors,
or #ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd
hate to be
forced to use something else to solve my reasons for using it to
begin with.

"SteveDB1" wrote:

Hi folks.
I use sumproduct to sum columns of values after testing against 2 criteria.
At first it was working great, and then it stopped working once one cell
that I was testing against had a value in it. It's actually become quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and cell b
would be the values that I'm looking for; and the Col&RowRangeFinal would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone so far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors, or #ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd hate to be
forced to use something else to solve my reasons for using it to begin with.

For some reason, I'm not able to see the OP, but am reading this through
Robin's post.

As in all cases, no information is passed to the group when you just say
"not working"!

You described the return of the unary version as a zero total, but you
didn't describe your problem with the asterisk version, except that it "only
works sometimes".

What happens when it (asterisk version) doesn't work?

Your zero total using the unary version leads me to believe that the data in
your totaling range are *all* text values.

This is partially substantiated by the fact that the asterisk version works
"sometimes".

The asterisk version will total numeric and text values, as long as they
even look like numbers.
The unary version will bypass anything that's text, and simply not calculate
them, with no warning or error messages.

The asterisk version, even though it will calculate almost anything that
even looks like a number, will error out if there is any value in the
totaling column that doesn't look like a number.
This includes nulls ( "" , zero length strings) that may be the returns of
formulas in the totaling column,
or invisible characters that may have been imported with the numbers from a
web site (notably Char(160)).

SO, if you're "not working", in relation to the asterisk form means a
#Value! error, look in your totaling column for non-numeric text, or web
imported characters.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"Robin McLean" <Robin wrote in message
...


"SteveDB1" wrote:

Hi folks.
I use sumproduct to sum columns of values after testing against 2
criteria.
At first it was working great, and then it stopped working once one cell
that I was testing against had a value in it. It's actually become quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and cell
b
would be the values that I'm looking for; and the Col&RowRangeFinal would
be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone so
far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be really
nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors, or
#ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd hate to
be
forced to use something else to solve my reasons for using it to begin
with.