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.