Solved

# Sumproduct not reading data from datatable

Posted on 2012-08-14
369 Views
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
Question by:Euro5

LVL 50

Accepted Solution

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 Closing Comment

Thanks so much!
0

## Featured Post

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.