Sum Formula

[Katich]

Hello,
I have a trial balance that i need to sum up the totals of the respective accounts. Each month there will be a new one pasted into the sheet so the range could change. So i need some kind of formula that will sum up all accounts that have a 9 digit account in from of the .00. I hope this makes sense.

Thank you in advance!

Book3
	C
4	380,554.71
Original TB - "Month"

 

[Katich]

Fluff's solution and the SumProduct solution work for earlier versions of Excel.

T202301a.xlsm
	E
2	100,156.27
3	100,156.27
4
5	-100,156.27
6	-100,156.27
7
2f

Cell Formulas
Range	Formula
E2	E2	=SUMIFS(B:B,A:A,">99999999")
E3	E3	=SUMPRODUCT(--(LEN(A3:A500)=9),B3:B500)
E5	E5	=SUMPRODUCT(--(LEN(A3:A500)=8),B3:B500)
E6	E6	=SUM((LEN(A3:A500)=8)*B3:B500)

Great! Thank you for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[awoohaw]

Why bother, there is no need to have a table?

since the user wants to continually add data the user must update the ranges when the ranges get used up. Additionally, if random junk gets placed in the ranges it may cause problems in the calcuations. Yes, it is rare that a 9 digit number in column A with other numbers in in column B or C to cause the problem. Only using cells that have the data you want is the safest method.