excel sumif not selecting correctly

Microsoft Excel 2003 (full product)
October 28, 2010 at 13:47:22
Specs: windows xp
My spreadsheet has a column on the left with a 21 digit field and a column on the right with a matching 21 digit field and a qty. The left has 1 instance, the right has 1 or many. I'm using sumif to sum the qty for all on the right that match one on the left.

i.e.

col A col B Col C Col D
506849879780781792233 sumif(c1:c3,a1,d1:d3) 506849879780781792233 10
506849879780781794444 sumif(c1:c3,a2,d1:d3) 506849879780781792233 2
506849879780781794444 5

For some reason my sumif is returning 17 for b1 and b2.
It should return 12 for b1 and 5 for b2.
I tried plugging the search string directly into the second parm of sumif as a constant, but it still has problem.
So it's like it is only matching on the 1st 17 characters.

Any ideas? Could this be text vs numeric format or something??

I've used this same spreadsheet before and it works fine.

Thanks.


See More: excel sumif not selecting correctly

Report •

#1
October 28, 2010 at 13:48:53
Sorry for the messed up formatting. Can you read it?

Report •

#2
October 28, 2010 at 13:55:58
col A ................................col B .........................Col C ............................Col D
506849879780781792233 ..sumif(c1:c3,a1,d1:d3) ..506849879780781792233 ..10
506849879780781794444 ..sumif(c1:c3,a2,d1:d3) ..506849879780781792233 ...2
..........................................................................506849879780781794444 ...5

Report •

#3
October 28, 2010 at 14:20:02
To post with your columns lined up:

1 - Click on the pre symbol located above the text entry box.
2 - Enter your data, in columns, between the pre tags. Use spaces not tabs.
3 - Click "Preview Follow Up" below the text entry box.
4 - Review how it looks and fix it in the text entry box below the Preview box.
5 - Click in the box next to "Check To Show Confirmation Page Again"
6 - Click either Confirm button to Preview the post again.
7 - Repeat step 4 - 6 as necessary until it looks the way you want.
8 - Click Confirm when you are satisfied.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 28, 2010 at 19:12:00
The limit of precision for numbers in Excel is 15.
Your 21 digit number 506849879780781792233
will get truncated to 506849879780781000000

Either convert to TEXT or
cut the number into two cells

          A             B
1) 506849879780781   792233

and use the unique sequence in B for your =SUMIF()

.

MIKE

http://www.skeptic.com/


Report •

Ask Question