Sumifs Bug Unable to count numbers with leading spaces

[micric0815]

I have the following problem. COUNTIFS does not seem to recognise that there is an actual difference between " 91640 " and "91640 ". Therefore the formula counts two instances of items that should appear only once. I would love to use SUMIFS as it is so much faster than the "regular" array formula.


<TABLE style="WIDTH: 413pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=549><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7277" width=199><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 38pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=50>Data</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54>Countifs</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=38>Array</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 156pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=208>Countifs</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 149pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=199>Array</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>91640 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>=COUNTIFS($A$2:$A$3;"="&$A2)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>{=SUM(($A$2:$A$3=C2)*(1))}</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>91640 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>=COUNTIFS($A$2:$A$3;"="&$A3)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>{=SUM(($A$2:$A$3=C3)*(1))}</TD></TR></TBODY></TABLE>

Formatting issues in the forum therefore to be 100% clear
<TABLE style="WIDTH: 413pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=549><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 38pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=50>Data........ </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54>Countifs</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=38>Array</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 156pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=208>Countifs </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 149pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=199>Array</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>" 91640 " </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>=COUNTIFS($A$2:$A$3;"="&$A2)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>{=SUM(($A$2:$A$3=C2)*(1))}</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>"91640 "</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>=COUNTIFS($A$2:$A$3;"="&$A3)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>{=SUM(($A$2:$A$3=C3)*(1))}</TD></TR></TBODY></TABLE>

Thank you very much in advance.

 

[RoryA]

I take it you can't replace the spaces with another character? SUMPRODUCT might be a bit faster than your SUM array formula.

 

[Aladin Akyurek]

Are you trying to count " 91640 " and "91640 " as different? If so, try:

=SUMPRODUCT((A2:A10=" 91640 ")+0)

=SUMPRODUCT((A2:A10="91640 ")+0)

 

[micric0815]

Thanks you both for the quick replies.
After seeing the Excel Formula Efficiency 12 series http://www.youtube.com/watch?v=yolP3Bapc-w I assumed I could just replace arrays with Sumifs.
But how to figure out 100% that this "error" will not occur in my other datasets and how to know when to replace Sumifs with Sumproduct?

So for the future there seems to be no alternative to good old and slow Sumproduct. I usually handle very big datasets so the improvement between Sumifs and Sumproduct would not have been marginal.

<TABLE style="WIDTH: 176pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=234><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><TBODY><TR style="HEIGHT: 45pt" height=60><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 64pt; HEIGHT: 45pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=60 width=85></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #002060; WIDTH: 44pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 width=59>Time</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #002060; WIDTH: 68pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 width=90>% Increase as compared to Fastest</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>SUM IF Array</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: red; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl70 align=right>0,240</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: red; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl69 align=right>373,020%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>SUM Array</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #ff9f9f; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl70 align=right>0,217</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #ff9f9f; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl69 align=right>327,303%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>SUMP *</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl70 align=right>0,214</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl69 align=right>323,145%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>SUMP --</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: yellow; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl70 align=right>0,212</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: yellow; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl69 align=right>319,243%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>DSUM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #c2d69a; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl70 align=right>0,055</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #c2d69a; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl69 align=right>7,895%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>SUMIFS</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: lime; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl70 align=right>0,051</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: lime; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-pattern: black none" class=xl69 align=right>0,000%</TD></TR></TBODY></TABLE>

 

[RoryA]

Then I'd use a helper column that replaces leading spaces with another character, and use sumif with that column as the criteria range. Or use a pivot table.

 

[micric0815]

Thanks rorya. My solution that I came up with is the following:

Option Explicit
Public arr As Variant

Private Sub CommandButton1_Click()
arr = Sheets("Table1").Range("A2:A3").Value
Sheets("Table1").Range("A2:A3").Replace What:=" ", Replacement:="#", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Then I do the calculation via SUMIFS and put the array back into the range again with

Private Sub CommandButton2_Click()
Sheets("Table1").Range("A2:A3").Value = arr
End Sub