• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

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))
0
Euro5
Asked:
Euro5
1 Solution
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now