#### johngr904

##### New Member
Hello,

I am trying to use sumif on columns of numbers as shown in the following example:

Acct # Amount
000123456 100.00
000123456 199.00
00123456 500.00
00123456 500.00

To get the total for each account number, I use sumif:

=sumif(a:a,a1,b:b) and paste the formula the rest of the way down in a new column to the right.

My problem is that sumif ignores the leading zeros and treats all of the account numbers shown in the example as the same account number.

In the system I am working in, they are distinct accounts, so I cannot remove or pad the number of zeros.

Any ideas on how to get Excel to recognize them as distinct numbers? I have tried converting them to text as well, didn't work.

Thanks

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### jbeaucaire

##### Well-known Member
That IS odd!? Even when formatted as text, it does that to me. Very interesting. You could use SUMPRODUCT instead with the text values, it doesn't seem to suffer the same problem.

=SUMPRODUCT(--(\$A\$1:\$A\$1000=A1),\$B\$1:\$B\$1000)

#### DonkeyOte

##### MrExcel MVP
Welcome to the Board.

One approach:

Excel Workbook
ABC
1AcctAmount
2000123456100299
3000123456199299
4001234565001000
5001234565001000
Sheet2

Not ideal though if you have lots of these formulae to do... I would suggest a Pivot Table.

#### johngr904

##### New Member
The formulas with sumproduct provided above provides the correct answer, as did an array formula I created that keyed off the account number and another column of information with distinguishing information.

However, the data is over 10,000 columns long, and that data also feeds into a pivot table on another tab. Whenever I try to run the formula as an array, or with sumproduct, it takes over an hour, maybe two, to complete the calculation.

Is there a way to speed that process up?

Thanks

#### DonkeyOte

##### MrExcel MVP
If you're using a PT already why do you need this summation column generated - ie why not do the aggregation via the PT ?

You're alternative to Sumproduct would be to have a helper column that say preceded each account with an alpha char - eg C2 = "a"&\$A2, then run SUMIF off the new column, eg D2 = SUMIF(\$C:\$C,\$C2,\$B:\$B)

Replies
4
Views
316
Replies
1
Views
521
Replies
1
Views
459
Replies
3
Views
242
Replies
2
Views
334

1,195,594
Messages
6,010,625
Members
441,558
Latest member
lambierules

### 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.

### Which adblocker are you using?

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

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