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???

Who is Participating?
barry houdiniCommented:
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.


That should work and it's faster too

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


regards, barry
Euro5Author Commented:
Thanks so much!
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.

All Courses

From novice to tech pro — start learning today.