SUMPRODUCT incorrectly returning 0

[AMeyers]

Hi, I've been struggling for a few days now with some SUMPRODUCT formulas that will only return zero. I simply need to sum all the numbers in column AA that have a particular date in column C. The formulas I've attempted to use include:

=SUMPRODUCT(('[Q SHIP LIST III.xlsx]SHIP LIST'!$AA$2:$AA$20000)*('[Q SHIP LIST III.xlsx]SHIP LIST'!$C$2:$C$20000 = A2))

=SUMPRODUCT(--('[Q SHIP LIST III.xlsx]SHIP LIST'!$C2:$C20000 = A2), '[Q SHIP LIST III.xlsx]SHIP LIST'!$AA2:$AA20000)

=SUMPRODUCT(--('[Q SHIP LIST III.xlsx]SHIP LIST'!$C2:$C20000 = A2),--('[Q SHIP LIST III.xlsx]SHIP LIST'!$AA2:$AA20000))

and many more, with multiple coercers. The value I always get is 0, not #VALUE! or #REF!.

The table I'm getting the data from is a query from a database system called Guardian, but I've made other SUMPRODUCT formulas that work on other Guardian queries just fine. I'm going crazy on this problem so any help would be much appreciated.

 

[Jonmo1]

Either
A) There are no dates in C2:C20000 equal to the value in A2
Is A2 (or the values in C2:C20000) just a date, or is it Date+Time ?
What do these return
=ISNUMBER(A2)
=ISNUMBER('[Q SHIP LIST III.xlsx]SHIP LIST'!$C2) <- filled down to end of data

B) There are no numeric values to sum in AA2:AA20000 corresponding to the dates matched in C2:C20000
What does this return
=ISNUMBER([Q SHIP LIST III.xlsx]SHIP LIST'!$AA2) <- filled down to end of data


Try SUMIF instead of sumproduct (it has more luck dealing with numbers stored as text)
=SUMIF('[Q SHIP LIST III.xlsx]SHIP LIST'!$C$2:$C$20000,A2,'[Q SHIP LIST III.xlsx]SHIP LIST'!$AA$2:$AA$20000)

 

[AMeyers]

Only =ISNUMBER('[Q SHIP LIST III.xlsx]SHIP LIST'!$C2:$C402) came back false. The dates in this column are formatted as 20140708 (for today). They are formatted as numbers, though, so why would that come back false? Also there are dates in C that equal A2 and I need to use sumproduct because I need this spreadsheet to update without opening the other file. Thank you for helping.

 

[Jonmo1]

=ISNUMBER('[Q SHIP LIST III.xlsx]SHIP LIST'!$C2:$C402)
This returned 0 because ISNUMBER can only evaluate a single cell.
What I asked was
=ISNUMBER('[Q SHIP LIST III.xlsx]SHIP LIST'!$C2)
Filled down to end of data so
=ISNUMBER('[Q SHIP LIST III.xlsx]SHIP LIST'!$C3)
=ISNUMBER('[Q SHIP LIST III.xlsx]SHIP LIST'!$C4)
=ISNUMBER('[Q SHIP LIST III.xlsx]SHIP LIST'!$C5)
etc...

Anyway, if the values in C2:C20000 are actually 20140708, then that is not actually a date. It's just a number.

What about the date in A2, is it written the same way?

 

[AMeyers]

=ISNUMBER('Q SHIP LIST III.xlsx]SHIP LIST'!$C2) and all the cells in C returned false. Yes, all the "dates" in A are written that way, too.

 

[Jonmo1]

Sounds like those date/number values are "Numbers Stored As Text"
Are they the result of a formula?

Try
=SUMPRODUCT(--('[Q SHIP LIST III.xlsx]SHIP LIST'!$C2:$C20000+0=A2+0),--('[Q SHIP LIST III.xlsx]SHIP LIST'!$AA2:$AA20000))

 

[AMeyers]

You're a life saver, that's exactly what I needed. Thank you so much!

 

[Jonmo1]

Glad to help, thanks for the feedback.

However, I'd recommend correcting the values at their source (A2 and C2:C20000), rather than adjusting for that problem in the formula.

Are they the result of another formula? You can probably just add +0 to the end of those formulas.

 

[AMeyers]

Well the values in C are pulled in via a query, so I assume they're incorrectly formatted in the database, which unfortunately I don't have the ability to change.

 

[Jonmo1]

Good enough.

You might not need the +0 after the A2 then, only the C values.