You shouldn't remove the , that is totally wrong. Can you lay some example
data out and tell us what you get with it, this seems silly the results you
are getting.

BTW is 9188 really text, or numbers?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Dan" wrote in message
...
No leading or trailing spaces the field is only 4 characters. I removed
the
comma so the line reads:

=SUMPRODUCT(--(TRIM(A2:A7)="9188")--(TRIM(B2:B7)="T"))

which works but when I substitute the "9188" with a cell reference I get
an
answer of 9. I think its multipling the number of 9188 with the number if
T
which would be 3*3


"Bob Phillips" wrote:

I would bet that the Access data has leading or trailing spaces, so use

=SUMPRODUCT(--(TRIM(A2:A7)="9188"),--(TRIM(B2:B7)="T"))

The last two fail because you cannot use whole columns in SUMPRODUCT,
only a
specific range.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Dan" wrote in message
...
I have data comming in to an excel file from Access. Once in excel the
spreadsheet does it's thing providing minor breakdowns with various
countif's, sumif's and graphs etc. I am new to the Sumproduct, the
following
is a sample of the data. I am, in this case looking for the number of
9188
and is T class. I have tried the formulas with and without quotes
around
9188:

=SUMPRODUCT(--(A2:A7="9188"),--(B2:B7="T"))
=SUMPRODUCT(A2:A7="9188")*(B2:B7="T")
=SUMPRODUCT(--(A2:A64000="9188"),--(B2:B64000="T"))
=SUMPRODUCT(A2:A64000="9188")*(B2:B64000="T")
=SUMPRODUCT(--(A:A="9188"),--(B:B="T")) result #NUM
=SUMPRODUCT(A:A="9188")*(B:B="T") result #NUM

A B
1 Number Class
2 9188 S
3 9188 D
4 9188 Z
5 9188 T
6 9188 T
7 9188 T

The first four give me a result of 0 instead of 3 and I think I
understand
the #NUM error. Cell formats are "General", Number field in Access is
text
if that helps(some numbers start with 0 which is why text). If there is
another way of going about this using a countif(AND( or something else
I
am
open to suggestions. Very much Thanks in advance