Sumproduct

[Jeff Podgorski]

I've tried and tried to make this work but each time it returns 0, please help.

=SUMPRODUCT(--(Data!M2:M15000=245),--(Data!N2:N15000=70110101),Data!O2:O15000)

Thanks

 

[T. Valko]

No leading zeros, out of the office now,will try in the morning.

Thanks

Ok, ideally, you'd want to convert the TEXT numbers in column N into numeric numbers.

Let's pick it back up tomorrow when you have access to the file.

 

[Jeff Podgorski]

Okay, I've gone through the rows more carefully and sure enough some of the lead with text, suggestions?

Ok, ideally, you'd want to convert the TEXT numbers in column N into numeric numbers.

Let's pick it back up tomorrow when you have access to the file.

 

[Datsmart]

Text Numbers Conversion Suggestions:

Suggestion 1. (convert by Text to Columns)
Select the range of text numbers,
Click Data | Text to Columns...
Then Click "Finish".

Suggestion 2. (convert by addition)
Select a blank cell
Hit Ctrl-C to copy
Select your number range
Right Click and choose Paste Special
Click Add, then OK.

Suggestion 3. (If you have Excel 2003 or newer)
Select the cells affected and click on the yellow "!" that shows up next to the cells and choose convert to number.
(Must have "Number stored as Text" checked in Tools|Options|Error Checking)
(Excel 2007 it is under "Excel Options"|"Formulas"|"Error checking rules")