Sumifs Bug Unable to count numbers with leading spaces

micric0815

New Member
Joined
Aug 2, 2010
Messages
3
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.
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Are you trying to count " 91640 " and "91640 " as different? If so, try:

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

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

micric0815

New Member
Joined
Aug 2, 2010
Messages
3
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Aug 2, 2010
Messages
3
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,616
Messages
5,832,716
Members
430,156
Latest member
Amosjack1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top