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&"")