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))
Euro5Asked:
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.

=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
 
Euro5Author 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.

All Courses

From novice to tech pro — start learning today.