Need Help = Sumif not working

[michael_allen_24]

Hey everyone,

Have a quick question. I have a report that is pulled for me from an online database and sent to me in Excel format (XLS). When I paste - special - values all of the data in the report I created, and try to run a sumif formula it does not pull back any data. But when I type a number in the section manually instead of relying on the pasted report, it does pull back the data. Any idea of why this would happen?

<TABLE style="WIDTH: 151pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=200><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 53pt" width=70><TBODY><TR style="HEIGHT: 11.65pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 98pt; HEIGHT: 11.65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=15 width=130>Primary Assignee</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 width=70>Open</TD></TR><TR style="HEIGHT: 11.65pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=15>Farnham, Ann</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77>1</TD></TR><TR style="HEIGHT: 11.65pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=15>Massey, Michele</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77>1</TD></TR><TR style="HEIGHT: 11.65pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=15>Schultz, Kay</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77>1</TD></TR><TR style="HEIGHT: 11.65pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=15>Klemansky, Michelle</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77>1</TD></TR><TR style="HEIGHT: 11.65pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=15>Thavaraj, Roy</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77>1</TD></TR><TR style="HEIGHT: 11.65pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=15>Thavaraj, Roy</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77>2</TD></TR><TR style="HEIGHT: 11.65pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=15>Schultz, Kay</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77>1</TD></TR><TR style="HEIGHT: 11.65pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=15>Schultz, Kay</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77>1</TD></TR></TBODY></TABLE>

Formula that I am using: =SUMIF(AI4:AI11,"Schultz,Kay",AJ4:AJ11)
Result = 0

Now if I manually type the numbers into the open column:
Result = 3

Any ideas?

 

[VoG]

You may have numbers stored as text. Try selecting them, Data > Text to Columns then click Finish.

 

[michael_allen_24]

Thanks for the reply. Didn't help. I checked in the options menu and couldn't find anything either.

 

[VoG]

Maybe they contain spaces or the dreaded CHAR(!60). Try selecting them then running the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

 

[michael_allen_24]

Wow it worked. Not sure why, but it worked.

Thanks for the help. What was the problem?

 

[VoG]

You had spaces - instead of "1", "1 " or similar.