Leading zeros and sumif

[johngr904]

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

 

[jbeaucaire]

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]

Welcome to the Board.

One approach:

Excel Workbook
	A	B	C
1	Acct	Amount
2	000123456	100	299
3	000123456	199	299
4	00123456	500	1000
5	00123456	500	1000
Sheet2

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

 

[johngr904]

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]

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)