# Sumproduct not reading data from datatable

The column B is a datatable from Access. The field is text format, as is \$D\$1 cell.
The result of the formula is 0.
HOWEVER, if I TYPE in the same data into any cell in column B, I will get the correct result from the formula.

For instance, the data in B is 120 and the result is 0.
If I simply TYPE in 120, it calculates correctly.

Also,  I am using a countif formula using the same column and IT calculates accurately.

Can anyone help???

=SUMPRODUCT((\$B\$13:\$B\$1048576=\$D\$1)*(\$H\$13:\$H\$1048576="Express")*(\$E\$13:\$E\$1048576))
###### Who is Participating?

Commented:
Sounds like column B is text formatted, COUNTIF will cope with that, SUMPRODUCT won't. I'd advise you to use SUMIFS function, i.e.

=SUMIFS(\$E\$13:\$E\$1048576,\$H\$13:\$H\$1048576,\$D\$1,\$E\$13:\$E\$1048576,"Express")

That should work and it's faster too

......or amend your SUMPRODUCT formula like this

=SUMPRODUCT((\$B\$13:\$B\$1048576+0=\$D\$1)*(\$H\$13:\$H\$1048576="Express")*(\$E\$13:\$E\$1048576))

regards, barry
0

Author Commented:
Thanks so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.