SUMPRODUCT Formula

[Guest]

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

 

[Guest]

are you sure there are no leading/trailing spaces in the data? you could use
TRIM to remove them (you could also use text to ensure the comparison is on a
text basis):
=SUMPRODUCT(--(TEXT(TRIM(A2:A7),"0000")="9188"),--(TRIM(B2:B7)="T"))

 

[Guest]

I think I got it I removed the ,from the ),--(B2.... so the formula is now:

=SUMPRODUCT(--(A2:A7="9188")--(B2:B7="T")) why do I now get a 9 when I
reference another sheet?

=SUMPRODUCT(--(A2:A7=Sheet1!H5)--(B2:B7="T")) Thanks again

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

try in a cell
=trim(a2)="9188"
in another
=trim(b5)="T"
I think you will find that the first cell read false and the second cell
reads true
by removing the comma, you are effectively using an eqaution similar to
=SUMPRODUCT(--(TRIM(A2:A7)="9188"))+sumproduct(--(TRIM(B2:B7)="T"))
your equation is counting tha number of times trim(A2... and Trim(b2... are
true indvidually.
if it responds 3 it indicates there are only three trues in the 12 possible
true false possibilities. if it responds 9 there are 9 trues.
the cell reference you used did the cell have 9188 in it?