SumIF not pulling all numbers from referenced data

[tiffer86]

I have a SUMIF that is pulling a 10 digit number (6100089103) there are 4 of these numbers that are very similar, replace the last number with 4, 5 and 6. My SumIF is Summing all 4 numbers even though they are not the full number I'm wanting it to pull.

Here is my formula: This is a concatenated # sitting in the first spreadsheet referenced in cell A18, the second spreadsheet (102014 OneOk Gas Check Detail Master.xls) it's find the number in Cell E and bringing back the Value sitting in L.


=SUMIF('[102014 OneOk Gas Check Detail Master.xls]Sheet1'!$E:$E,A18,'[102014 OneOk Gas Check Detail Master.xls]Sheet1'!$L:$L)

Instead of it pulling back $75,000 it's pulling back all 4 numbers that start with 910008910

 

[Jonmo1]

Which number is actually in A18?

I have a SUMIF that is pulling a 10 digit number (6100089103)
it's pulling back all 4 numbers that start with 910008910

 

[tiffer86]

Which number is actually in A18?

It is a concatenated number: 1020146100089103

 

[Jonmo1]

If the values in column E are 10 digit numbers..
I don't see how that formula could be returning anything if the criteria (A18) of the formula is a 16 digit text string (concatenate returns TEXT)
None of the criteria would be met.

We're going to need to see some sample data, try using the Excel Jeanie to post a sample sheet.
See my signature for a link to it.

 

[tiffer86]

Sorry, it finds that 16 digit number, not 10 in the 2nd spreadsheet and it's the 10 digit # after 102014 that is adding everything up

 

[Jonmo1]

Sorry, can't visualize the issue.

Can you post a sample with the Excel Jeanie, or post a copy of the book to a file sharing site like box.net

 

[tiffer86]

https://app.box.com/s/e1l7ibbomiuggcmjdyd7

 

[Jonmo1]

I was thinking more along the lines of an EXCEL book, not a word document with a picture in it.
Sorry, but pictures don't really help alot.

 

[tiffer86]

https://app.box.com/s/i8kc8csna8023z53lrqa

 

[Jonmo1]

I do think I see the problem though.

Excel only calclulates 15 significant digits.
Even though you have the values stored as TEXT
the SUMIF function is converting them to numbers, and truncing off the 16th digit as a result of that.

Try sumproduct instead

=SUMPRODUCT(--('[102014 OneOk Gas Check Detail Master.xls]Sheet1'!$E:$E=A18),'[102014 OneOk Gas Check Detail Master.xls]Sheet1'!$L:$L)


Though I highly recommend NOT using entire column references like E:E with sumproduct, use specific row numbers like E1:E100
=SUMPRODUCT(--('[102014 OneOk Gas Check Detail Master.xls]Sheet1'!$E$1:$E$100=A18),'[102014 OneOk Gas Check Detail Master.xls]Sheet1'!$L$1:$L$100)