SUMPRODUCT returning 0

[tjiddy89]

Hello all! Just started learning more formulas and VBA scripting for my job, but I'm running into an issue with the SUMPRODUCT formula returning 0 instead of the actual number. My intent was to have this run through the specified closed workbook and range and then return the number of cells with a value of 2.

=SUMPRODUCT('C:\Desktop\[test.xlsx]Sheet1'!$D$2:$D$85="2")

Also, I've verified the sourced data in John test.xlsx only contain numerical characters but are formatted as "General". Could this by chance be the culprit? Thanks a ton in advance for your help!

 

[dreid1011]

Hello all! Just started learning more formulas and VBA scripting for my job, but I'm running into an issue with the SUMPRODUCT formula returning 0 instead of the actual number. My intent was to have this run through the specified closed workbook and range and then return the number of cells with a value of 2.

=SUMPRODUCT('C:\Desktop\[test.xlsx]Sheet1'!$D$2:$D$85="2")

Also, I've verified the sourced data in John test.xlsx only contain numerical characters but are formatted as "General". Could this by chance be the culprit? Thanks a ton in advance for your help!

Welcome to the board.

Most likely, yes. Your formula is only going to match values of 2 stored as text because of the " ". Remove them from around the 2, and you should see a different result.

 

[tjiddy89]

Welcome to the board.

Most likely, yes. Your formula is only going to match values of 2 stored as text because of the " ". Remove them from around the 2, and you should see a different result.

Still no dice unfortunately. I've even attempted to change the referenced source data to Number but it still returns 0.

 

[shg]

=SUMPRODUCT(--('C:\Desktop\[test.xlsx]Sheet1'!$D$2:$D$85=2))

Edit: Assuming you're looking for the number 2, not "2"

 

[dreid1011]

And by verified, have you tried =ISNUMBER(A1) and or =A1=2? A1 being whichever cell contains a 2 you are trying to match.

 

[tjiddy89]

=SUMPRODUCT(--('C:\Desktop\[test.xlsx]Sheet1'!$D$2:$D$85=2))

Edit: Assuming you're looking for the number 2, not "2"

That worked perfectly! Thank you! What exactly are the extra characters for? The "--(" and extra parenthesis on the end?

 

[shg]

The expression returns TRUE or FALSE; the double negation converts those to 1 and 0.