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"

 

[Joe4]

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.

I don't know if that makes sense, especially since the cell you show in your example does not have 9 digits in front of the .00.

Perhaps you meant to select a large range to copy?
Make sure that you have selected more than one cell before you copy/paste it.

 

[Katich]

I don't know if that makes sense, especially since the cell you show in your example does not have 9 digits in front of the .00.

Perhaps you meant to select a large range to copy?
Make sure that you have selected more than one cell before you copy/paste it.

Sorry about that! How's this?

Book3
	A	B	C
1		100,156.27	-230,621.46
2	Account #	Period To Date	Year To Date
3	11010000.000	0.00	6,000.00
4	11020000.000	150,055.85	380,554.71
5	11201000.000	-20,824.02	-26,959.41
6	11202000.000	-8,427.34	85,710.18
7	11261000.000	0.00	54,204.96
8	11290000.000	-13,371.09	-58,120.10
9	11410000.000	169.69	22,494.76
10	11420001.000	312.97	2,721.01
11	11420002.000	-324.60	3,939.97
12	11420003.000	-374.78	10,723.55
13	11440000.000	556.44	2,011.11
14	11501000.000	-7,120.75	0.00
15	11505500.000	0.00	2,287.00
16	22016000.000	3,739.08	-28,957.31
17	22017000.000	-1,071.00	-12,520.00
18	22019000.000	-15,274.68	-34,150.03
19	22020000.000	-35,166.08	-121,700.45
20	22024000.000	0.00	-10,682.02
21	22101000.000	-8,668.22	-85,408.22
22	22103000.000	4,080.94	-929.94
23	22103500.000	0.00	-12,519.11
24	22105000.000	-31,462.40	-61,909.46
25	22106500.000	0.00	-16,016.20
26	22107000.000	-1,575.28	-42,927.67
27	22108000.000	-2,183.66	-2,183.66
28	22200000.000	-7,933.21	-13,636.78
29	22201000.000	-116,985.36	-190,500.00
30	22203000.000	-1,448.00	-11,569.00
31	22204000.000	-1,922.00	-37,276.00
32	22206001.000	587.20	-15,204.92
33	22206003.000	-9,087.11	-9,087.11
34	22210000.000	-13,745.80	-111,437.43
35	22310007.000	4,172.00	-5,280.00
36	22310020.000	33,134.94	0.00
37	22530000.000	0.00	555,771.53
38	22535000.000	0.00	-520,000.00
39	22537500.000	0.00	-116,900.00
40	22540000.000	0.00	650,077.50
41	103101000.000	-80,851.66	-1,134,433.80
42	103101500.000	-50,096.56	-1,018,318.54
43	103102000.000	-3,677.92	-63,028.44
44	103102500.000	-28,644.53	-292,215.55
45	103103000.000	-1,682.36	-20,855.77
46	103105500.000	-79,555.00	-113,387.25
47	103106000.000	0.00	-4,469.00
48	103106500.000	-38,620.00	-1,088,186.50
49	103108000.000	-356.89	-9,991.34
50	103109000.000	-2,786.01	-29,599.81
51	103109100.000	0.00	-242.00
52	103109200.000	0.00	-228.72
53	103109300.000	-58.86	-3,102.48
54	105000100.000	847.00	11,164.00
55	105000200.000	9,642.79	109,565.82
56	105105000.000	2,736.18	56,476.71
57	105105100.000	10,046.49	113,396.20
58	105108000.000	2,813.50	59,252.27
59	105108100.000	8,788.25	123,842.18
60	105108300.000	2,848.41	54,249.96
61	105108800.000	3,095.20	35,298.06
62	105949800.000	0.00	1,923.97
63	105949900.000	6,640.78	100,407.10
64	105951000.000	3,404.50	44,130.77
65	105951100.000	126.20	1,221.19
66	105951200.000	1,278.12	6,396.29
67	105952100.000	4,201.87	33,341.72
68	105953000.000	354.30	4,636.72
69	105953200.000	105.00	598.41
70	105953300.000	1,768.35	37,773.42
71	105953600.000	583.14	8,778.84
72	106016000.000	2,291.50	18,149.48
73	106020000.000	0.00	10,617.04
74	106022000.000	3,357.27	89,309.43
75	106022500.000	0.00	4,149.49
76	106022900.000	5,046.35	53,427.23
77	106023000.000	0.00	5,527.54
78	106024000.000	0.00	6,972.80
79	106028000.000	58.77	58.77
80	106036000.000	0.00	135.21
81	106048000.000	0.00	1,472.17
82	106049000.000	4,371.10	39,977.57
83	106050000.000	0.00	120.57
84	106059000.000	2,776.77	39,936.29
85	106063000.000	0.00	258.55
86	106066000.000	0.00	20,628.81
87	106072000.000	0.00	1,873.14
88	106074000.000	2,941.55	34,161.52
89	106075000.000	512.95	5,549.20
90	106084000.000	0.00	89.46
91	106089000.000	1,556.06	16,105.99
92	106100000.000	0.00	2,973.48
93	106102000.000	0.00	2,182.93
94	203200510.000	-2,732.87	-66,445.48
95	203200560.000	-375.00	-1,974.95
96	203200580.000	-4,410.00	-65,325.10
97	203201010.000	-9,278.15	-116,398.40
98	203201510.000	-7,855.15	-69,877.01
99	203201560.000	-174.50	-424.68
100	203201580.000	-4,885.34	-91,586.04
101	203202010.000	-17,235.88	-218,314.36
102	203202060.000	-854.54	-3,993.78
103	203202080.000	-41,908.61	-509,754.51
104	203202580.000	-18,367.40	-123,319.55
105	203203095.000	9.00	39.89
106	203205080.000	-836.10	-17,630.80
107	203206080.000	-5,975.00	-57,915.36
108	203208080.000	-23,405.41	-232,391.06
109	203209080.000	-150.00	-3,164.02
110	203300010.000	0.00	-123.20
111	203300060.000	0.00	12.00
112	203300080.000	0.00	-16,948.05
113	203301010.000	-14,127.33	-126,917.73
114	203301060.000	-22.00	-270.00
115	203301080.000	-3,682.00	-66,979.21
116	203302010.000	-9,670.06	-69,403.15
117	203302060.000	-35.00	-118.00
118	203302080.000	-3,848.00	-71,657.71
119	203303010.000	-31,521.39	-284,015.37
120	203303060.000	-10.00	-10.00
121	203303080.000	-4,080.00	-72,738.01
122	203304096.000	0.00	-20.23
123	204200000.000	49,862.63	430,993.76
124	204201000.000	320.00	15,266.84
125	204300000.000	518.34	21,652.34
126	204301000.000	3,451.94	31,185.44
127	204302000.000	2,479.38	28,856.58
128	204303000.000	5,445.18	61,002.76
129	205000100.000	1,184.00	15,208.00
130	205000200.000	20,194.99	184,136.62
131	205203000.000	9,524.81	75,791.46
132	205203200.000	4,447.71	66,375.38
133	205204010.000	5,476.50	122,798.13
134	205204070.000	2,815.93	44,246.85
135	205204110.000	5,667.56	58,022.85
136	205204170.000	2,297.53	21,884.54
137	205204210.000	-12.00	9,745.52
138	205204370.000	1,659.33	10,697.30
139	205205070.000	2,013.60	32,411.84
140	205207070.000	13,242.27	123,336.17
141	205301091.000	1,643.97	39,357.61
142	205949900.000	214.02	67,738.61
143	205951000.000	6,277.40	75,572.33
144	205951100.000	239.75	1,683.62
145	205951200.000	2,275.77	8,799.06
146	205952100.000	6,412.42	34,614.78
147	205953000.000	312.44	3,594.11
148	205953200.000	45.00	1,932.13
149	205953300.000	4,185.71	49,734.29
150	205953600.000	190.16	2,661.32
151	206014000.000	0.00	2,923.31
152	206019000.000	0.00	130.92
153	206020000.000	407.73	12,059.73
154	206023000.000	0.00	2,501.73
155	206024000.000	669.50	3,006.10
156	206028000.000	0.00	81.86
157	206030000.000	517.89	7,910.38
158	206036000.000	0.00	1,399.87
159	206038000.000	0.00	-274.61
160	206044000.000	30.09	1,135.14
161	206058000.000	793.01	5,433.65
162	206058500.000	360.81	5,960.24
163	206059000.000	0.00	478.00
164	206063000.000	0.00	6,014.41
165	206066000.000	6,709.84	54,676.50
166	206071000.000	854.70	12,126.72
167	206072000.000	0.00	59.60
168	206073000.000	0.00	808.60
169	206074000.000	1,495.72	13,306.07
170	206079000.000	2,754.84	31,064.55
171	206084000.000	0.00	966.83
172	206097000.000	0.00	604.80
173	206099000.000	0.00	272.12
174	206100000.000	0.00	3,815.52
175	206102000.000	1,125.82	11,850.11
176	206103000.000	555.13	14,402.19
177	483482000.000	-210.24	-9,723.95
178	483482001.000	0.00	-600.00
179	483482500.000	0.00	-11,124.00
180	483483500.000	-21.30	-228.22
181	483485000.000	-4,780.97	-27,728.67
182	483485500.000	0.00	8.00
183	483485600.000	0.00	-137.24
184	483487200.000	-295.47	-2,419.74
185	483487500.000	-3,748.32	-48,377.31
186	483488500.000	0.00	763.58
187	484487500.000	1,905.56	30,060.10
188	505000100.000	24,814.00	58,902.00
189	505000200.000	15,762.17	177,986.67
190	505503500.000	1,774.44	27,346.99
191	505951000.000	998.48	20,294.84
192	505951100.000	48.54	89.65
193	505951200.000	469.95	673.73
194	505952100.000	1,514.10	11,681.37
195	505953000.000	499.31	3,271.12
196	505953100.000	103.51	575.85
197	505953200.000	60.00	265.07
198	505953300.000	730.64	13,518.42
199	505953600.000	202.83	2,800.00
200	506013000.000	1,805.33	5,521.63
201	506017000.000	104.33	93.99
202	506018000.000	4,209.33	50,511.96
203	506023000.000	0.00	420.76
204	506025000.000	824.75	11,023.03
205	506027000.000	11,552.65	141,781.78
206	506031000.000	9.00	189.00
207	506036000.000	1,535.68	5,587.43
208	506051000.000	701.43	18,845.82
209	506062000.000	11.81	18,847.78
210	506063000.000	0.00	1,516.39
211	506067000.000	0.00	127.79
212	506072000.000	300.14	795.11
213	506074000.000	860.12	3,744.86
214	506080000.000	946.06	10,921.08
215	506083000.000	84.73	790.95
216	506087000.000	13,371.09	31,663.73
217	506087100.000	621.90	7,215.90
218	506091000.000	5,590.26	72,092.33
219	506097000.000	0.00	2,121.94
220	506099000.000	96.13	473.46
221	506100000.000	440.10	6,253.73
222	524521000.000	2,116.61	23,821.16
223	524522000.000	329.97	3,771.93
224	526024000.000	64.55	2,924.40
225	526025000.000	183.68	2,204.16
226	526521000.000	475.00	5,463.65
227	526521500.000	0.00	2,178.15
228	526522000.000	719.99	13,837.93
229	526522500.000	275.14	1,575.01
230	526523000.000	337.46	6,987.62
231	526523500.000	2,389.39	16,662.64
232	526524000.000	0.00	1,546.39
233	526524500.000	240.00	6,547.77
234	526525000.000	0.00	12,899.10
235	556551000.000	12,520.00	154,131.00
236	605000100.000	3,449.00	14,101.99
237	605000200.000	9,958.32	174,943.07
238	605608000.000	2,833.69	55,845.25
239	605951000.000	1,218.03	21,936.91
240	605951100.000	44.76	227.30
241	605951200.000	440.62	1,229.33
242	605952100.000	2,981.97	17,963.00
243	605953000.000	328.56	5,940.72
244	605953200.000	90.00	-395.32
245	605953300.000	588.55	15,305.85
246	605953600.000	152.12	2,104.86
247	606021010.000	159.32	784.84
248	606023000.000	0.00	937.31
249	606024000.000	1,950.00	13,285.48
250	606025000.000	2,960.01	35,905.90
251	606028000.000	0.00	21.35
252	606031000.000	406.30	10,391.09
253	606040010.000	11,461.14	151,956.08
254	606041010.000	8,595.88	113,967.90
255	606055010.000	0.00	4,145.15
256	606068010.000	9,374.85	112,414.85
257	606070010.000	800.00	14,407.97
258	606070011.000	971.71	12,088.54
259	606072000.000	0.00	500.00
260	606074000.000	312.09	6,383.08
261	606075010.000	0.00	1,295.00
262	606076000.000	0.00	960.00
263	606077010.000	0.00	10,047.70
264	606083000.000	0.00	77.64
265	606086000.000	0.00	2,718.05
266	606096000.000	0.00	3,368.81
267	606097000.000	272.07	679.08
268	606099000.000	0.00	762.58
269	606100000.000	0.00	5,102.94
270	606100500.000	0.00	2,725.00
271	705000100.000	560.00	7,720.00
272	705000200.000	5,068.38	61,976.77
273	705702000.000	3,390.59	42,558.07
274	705703000.000	3,667.00	44,151.74
275	705951000.000	1,088.78	12,940.14
276	705951100.000	27.05	228.05
277	705951200.000	396.16	1,273.90
278	705952100.000	1,335.45	11,253.99
279	705953000.000	172.62	2,139.31
280	705953200.000	75.00	271.14
281	705953300.000	786.80	10,525.45
282	705953600.000	139.45	1,899.18
283	706015000.000	520.51	13,754.91
284	706024000.000	1,077.56	47,117.44
285	706033000.000	224.61	3,107.78
286	706034000.000	1,092.00	16,722.12
287	706035000.000	81.22	10,762.55
288	706036000.000	0.00	472.37
289	706039000.000	128.60	405.18
290	706042000.000	0.00	2,403.40
291	706047000.000	1,328.53	20,765.30
292	706052000.000	0.00	7,713.80
293	706057000.000	0.00	461.87
294	706060000.000	13.69	7,788.37
295	706063000.000	0.00	1,653.75
296	706064000.000	0.00	8,570.32
297	706065000.000	0.00	4,860.88
298	706072000.000	0.00	1,880.78
299	706074000.000	330.55	3,960.09
300	706078000.000	0.00	2,169.91
301	706082000.000	0.00	9,719.99
302	706093000.000	0.00	4,341.01
303	706097000.000	0.00	300.00
304	706100000.000	0.00	8.05
305	706102000.000	498.90	7,392.35
306	706702500.000	1,767.81	18,803.92
307	756751000.000	14,271.09	140,525.26
308	756752000.000	13,001.66	76,989.76
309	756755000.000	3,977.15	20,603.89
310	756758500.000	42.85	524.47
311	806802500.000	0.00	3,888.11
312	806805000.000	116,985.36	444,863.66
313	806809000.000	7,120.75	60,368.78
314	915912000.000	1,335.70	21,012.00
315	915951000.000	129.72	1,857.94
316	915951100.000	6.43	43.27
317	915951200.000	57.44	219.87
318	915952100.000	523.40	1,794.44
319	915953200.000	-45.00	-210.00
320	915953300.000	109.24	1,140.91
321	916061000.000	659.84	13,427.02
322	916911000.000	-2,776.77	-39,285.45
Original TB - "Month"

Cell Formulas
Range	Formula
B1:C1	B1	=SUM(B41:B322)

 

[Dave Patton]

Select the correct range. You can use a large number for the last row as long as you do not put numbers below the range you mean to sum.

T202301a.xlsm
	A	B	C
1		0.00	0.00
2	Account #	Period To Date	Year To Date
3	11010000	0.00	6,000.00
4	11020000	150,055.85	380,554.71
5	11201000	-20,824.02	-26,959.41
6	11202000	-8,427.34	85,710.18
7	11261000	0.00	54,204.96
8	11290000	-13,371.09	-58,120.10
9	11410000	169.69	22,494.76
10	11420001	312.97	2,721.01
11	11420002	-324.60	3,939.97
12	11420003	-374.78	10,723.55
13	11440000	556.44	2,011.11
14	11501000	-7,120.75	0.00
15	11505500	0.00	2,287.00
16	22016000	3,739.08	-28,957.31
17	22017000	-1,071.00	-12,520.00
18	22019000	-15,274.68	-34,150.03
19	22020000	-35,166.08	-121,700.45
20	22024000	0.00	-10,682.02
21	22101000	-8,668.22	-85,408.22
22	22103000	4,080.94	-929.94
23	22103500	0.00	-12,519.11
24	22105000	-31,462.40	-61,909.46
25	22106500	0.00	-16,016.20
26	22107000	-1,575.28	-42,927.67
27	22108000	-2,183.66	-2,183.66
28	22200000	-7,933.21	-13,636.78
29	22201000	-116,985.36	-190,500.00
30	22203000	-1,448.00	-11,569.00
31	22204000	-1,922.00	-37,276.00
32	22206001	587.20	-15,204.92
33	22206003	-9,087.11	-9,087.11
34	22210000	-13,745.80	-111,437.43
35	22310007	4,172.00	-5,280.00
36	22310020	33,134.94	0.00
37	22530000	0.00	555,771.53
38	22535000	0.00	-520,000.00
39	22537500	0.00	-116,900.00
40	22540000	0.00	650,077.50
41	103101000	-80,851.66	-1,134,433.80
42	103101500	-50,096.56	-1,018,318.54
43	103102000	-3,677.92	-63,028.44
44	103102500	-28,644.53	-292,215.55
45	103103000	-1,682.36	-20,855.77
46	103105500	-79,555.00	-113,387.25
47	103106000	0.00	-4,469.00
48	103106500	-38,620.00	-1,088,186.50
49	103108000	-356.89	-9,991.34
50	103109000	-2,786.01	-29,599.81
51	103109100	0.00	-242.00
52	103109200	0.00	-228.72
53	103109300	-58.86	-3,102.48
54	105000100	847.00	11,164.00
55	105000200	9,642.79	109,565.82
56	105105000	2,736.18	56,476.71
57	105105100	10,046.49	113,396.20
58	105108000	2,813.50	59,252.27
59	105108100	8,788.25	123,842.18
60	105108300	2,848.41	54,249.96
61	105108800	3,095.20	35,298.06
62	105949800	0.00	1,923.97
63	105949900	6,640.78	100,407.10
64	105951000	3,404.50	44,130.77
65	105951100	126.20	1,221.19
66	105951200	1,278.12	6,396.29
67	105952100	4,201.87	33,341.72
68	105953000	354.30	4,636.72
69	105953200	105.00	598.41
70	105953300	1,768.35	37,773.42
71	105953600	583.14	8,778.84
72	106016000	2,291.50	18,149.48
73	106020000	0.00	10,617.04
74	106022000	3,357.27	89,309.43
75	106022500	0.00	4,149.49
76	106022900	5,046.35	53,427.23
77	106023000	0.00	5,527.54
78	106024000	0.00	6,972.80
79	106028000	58.77	58.77
80	106036000	0.00	135.21
81	106048000	0.00	1,472.17
82	106049000	4,371.10	39,977.57
83	106050000	0.00	120.57
84	106059000	2,776.77	39,936.29
85	106063000	0.00	258.55
86	106066000	0.00	20,628.81
87	106072000	0.00	1,873.14
88	106074000	2,941.55	34,161.52
89	106075000	512.95	5,549.20
90	106084000	0.00	89.46
91	106089000	1,556.06	16,105.99
92	106100000	0.00	2,973.48
93	106102000	0.00	2,182.93
94	203200510	-2,732.87	-66,445.48
95	203200560	-375.00	-1,974.95
96	203200580	-4,410.00	-65,325.10
97	203201010	-9,278.15	-116,398.40
98	203201510	-7,855.15	-69,877.01
99	203201560	-174.50	-424.68
100	203201580	-4,885.34	-91,586.04
101	203202010	-17,235.88	-218,314.36
102	203202060	-854.54	-3,993.78
103	203202080	-41,908.61	-509,754.51
104	203202580	-18,367.40	-123,319.55
105	203203095	9.00	39.89
106	203205080	-836.10	-17,630.80
107	203206080	-5,975.00	-57,915.36
108	203208080	-23,405.41	-232,391.06
109	203209080	-150.00	-3,164.02
110	203300010	0.00	-123.20
111	203300060	0.00	12.00
112	203300080	0.00	-16,948.05
113	203301010	-14,127.33	-126,917.73
114	203301060	-22.00	-270.00
115	203301080	-3,682.00	-66,979.21
116	203302010	-9,670.06	-69,403.15
117	203302060	-35.00	-118.00
118	203302080	-3,848.00	-71,657.71
119	203303010	-31,521.39	-284,015.37
120	203303060	-10.00	-10.00
121	203303080	-4,080.00	-72,738.01
122	203304096	0.00	-20.23
123	204200000	49,862.63	430,993.76
124	204201000	320.00	15,266.84
125	204300000	518.34	21,652.34
126	204301000	3,451.94	31,185.44
127	204302000	2,479.38	28,856.58
128	204303000	5,445.18	61,002.76
129	205000100	1,184.00	15,208.00
130	205000200	20,194.99	184,136.62
131	205203000	9,524.81	75,791.46
132	205203200	4,447.71	66,375.38
133	205204010	5,476.50	122,798.13
134	205204070	2,815.93	44,246.85
135	205204110	5,667.56	58,022.85
136	205204170	2,297.53	21,884.54
137	205204210	-12.00	9,745.52
138	205204370	1,659.33	10,697.30
139	205205070	2,013.60	32,411.84
140	205207070	13,242.27	123,336.17
141	205301091	1,643.97	39,357.61
142	205949900	214.02	67,738.61
143	205951000	6,277.40	75,572.33
144	205951100	239.75	1,683.62
145	205951200	2,275.77	8,799.06
146	205952100	6,412.42	34,614.78
147	205953000	312.44	3,594.11
148	205953200	45.00	1,932.13
149	205953300	4,185.71	49,734.29
150	205953600	190.16	2,661.32
151	206014000	0.00	2,923.31
152	206019000	0.00	130.92
153	206020000	407.73	12,059.73
154	206023000	0.00	2,501.73
155	206024000	669.50	3,006.10
156	206028000	0.00	81.86
157	206030000	517.89	7,910.38
158	206036000	0.00	1,399.87
159	206038000	0.00	-274.61
160	206044000	30.09	1,135.14
161	206058000	793.01	5,433.65
162	206058500	360.81	5,960.24
163	206059000	0.00	478.00
164	206063000	0.00	6,014.41
165	206066000	6,709.84	54,676.50
166	206071000	854.70	12,126.72
167	206072000	0.00	59.60
168	206073000	0.00	808.60
169	206074000	1,495.72	13,306.07
170	206079000	2,754.84	31,064.55
171	206084000	0.00	966.83
172	206097000	0.00	604.80
173	206099000	0.00	272.12
174	206100000	0.00	3,815.52
175	206102000	1,125.82	11,850.11
176	206103000	555.13	14,402.19
177	483482000	-210.24	-9,723.95
178	483482001	0.00	-600.00
179	483482500	0.00	-11,124.00
180	483483500	-21.30	-228.22
181	483485000	-4,780.97	-27,728.67
182	483485500	0.00	8.00
183	483485600	0.00	-137.24
184	483487200	-295.47	-2,419.74
185	483487500	-3,748.32	-48,377.31
186	483488500	0.00	763.58
187	484487500	1,905.56	30,060.10
188	505000100	24,814.00	58,902.00
189	505000200	15,762.17	177,986.67
190	505503500	1,774.44	27,346.99
191	505951000	998.48	20,294.84
192	505951100	48.54	89.65
193	505951200	469.95	673.73
194	505952100	1,514.10	11,681.37
195	505953000	499.31	3,271.12
196	505953100	103.51	575.85
197	505953200	60.00	265.07
198	505953300	730.64	13,518.42
199	505953600	202.83	2,800.00
200	506013000	1,805.33	5,521.63
201	506017000	104.33	93.99
202	506018000	4,209.33	50,511.96
203	506023000	0.00	420.76
204	506025000	824.75	11,023.03
205	506027000	11,552.65	141,781.78
206	506031000	9.00	189.00
207	506036000	1,535.68	5,587.43
208	506051000	701.43	18,845.82
209	506062000	11.81	18,847.78
210	506063000	0.00	1,516.39
211	506067000	0.00	127.79
212	506072000	300.14	795.11
213	506074000	860.12	3,744.86
214	506080000	946.06	10,921.08
215	506083000	84.73	790.95
216	506087000	13,371.09	31,663.73
217	506087100	621.90	7,215.90
218	506091000	5,590.26	72,092.33
219	506097000	0.00	2,121.94
220	506099000	96.13	473.46
221	506100000	440.10	6,253.73
222	524521000	2,116.61	23,821.16
223	524522000	329.97	3,771.93
224	526024000	64.55	2,924.40
225	526025000	183.68	2,204.16
226	526521000	475.00	5,463.65
227	526521500	0.00	2,178.15
228	526522000	719.99	13,837.93
229	526522500	275.14	1,575.01
230	526523000	337.46	6,987.62
231	526523500	2,389.39	16,662.64
232	526524000	0.00	1,546.39
233	526524500	240.00	6,547.77
234	526525000	0.00	12,899.10
235	556551000	12,520.00	154,131.00
236	605000100	3,449.00	14,101.99
237	605000200	9,958.32	174,943.07
238	605608000	2,833.69	55,845.25
239	605951000	1,218.03	21,936.91
240	605951100	44.76	227.30
241	605951200	440.62	1,229.33
242	605952100	2,981.97	17,963.00
243	605953000	328.56	5,940.72
244	605953200	90.00	-395.32
245	605953300	588.55	15,305.85
246	605953600	152.12	2,104.86
247	606021010	159.32	784.84
248	606023000	0.00	937.31
249	606024000	1,950.00	13,285.48
250	606025000	2,960.01	35,905.90
251	606028000	0.00	21.35
252	606031000	406.30	10,391.09
253	606040010	11,461.14	151,956.08
254	606041010	8,595.88	113,967.90
255	606055010	0.00	4,145.15
256	606068010	9,374.85	112,414.85
257	606070010	800.00	14,407.97
258	606070011	971.71	12,088.54
259	606072000	0.00	500.00
260	606074000	312.09	6,383.08
261	606075010	0.00	1,295.00
262	606076000	0.00	960.00
263	606077010	0.00	10,047.70
264	606083000	0.00	77.64
265	606086000	0.00	2,718.05
266	606096000	0.00	3,368.81
267	606097000	272.07	679.08
268	606099000	0.00	762.58
269	606100000	0.00	5,102.94
270	606100500	0.00	2,725.00
271	705000100	560.00	7,720.00
272	705000200	5,068.38	61,976.77
273	705702000	3,390.59	42,558.07
274	705703000	3,667.00	44,151.74
275	705951000	1,088.78	12,940.14
276	705951100	27.05	228.05
277	705951200	396.16	1,273.90
278	705952100	1,335.45	11,253.99
279	705953000	172.62	2,139.31
280	705953200	75.00	271.14
281	705953300	786.80	10,525.45
282	705953600	139.45	1,899.18
283	706015000	520.51	13,754.91
284	706024000	1,077.56	47,117.44
285	706033000	224.61	3,107.78
286	706034000	1,092.00	16,722.12
287	706035000	81.22	10,762.55
288	706036000	0.00	472.37
289	706039000	128.60	405.18
290	706042000	0.00	2,403.40
291	706047000	1,328.53	20,765.30
292	706052000	0.00	7,713.80
293	706057000	0.00	461.87
294	706060000	13.69	7,788.37
295	706063000	0.00	1,653.75
296	706064000	0.00	8,570.32
297	706065000	0.00	4,860.88
298	706072000	0.00	1,880.78
299	706074000	330.55	3,960.09
300	706078000	0.00	2,169.91
301	706082000	0.00	9,719.99
302	706093000	0.00	4,341.01
303	706097000	0.00	300.00
304	706100000	0.00	8.05
305	706102000	498.90	7,392.35
306	706702500	1,767.81	18,803.92
307	756751000	14,271.09	140,525.26
308	756752000	13,001.66	76,989.76
309	756755000	3,977.15	20,603.89
310	756758500	42.85	524.47
311	806802500	0.00	3,888.11
312	806805000	116,985.36	444,863.66
313	806809000	7,120.75	60,368.78
314	915912000	1,335.70	21,012.00
315	915951000	129.72	1,857.94
316	915951100	6.43	43.27
317	915951200	57.44	219.87
318	915952100	523.40	1,794.44
319	915953200	-45.00	-210.00
320	915953300	109.24	1,140.91
321	916061000	659.84	13,427.02
322	916911000	-2,776.77	-39,285.45
323
2f

Cell Formulas
Range	Formula
B1:C1	B1	=SUM(B3:B500)

 

[Katich]

Select the correct range. You can use a large number for the last row as long as you do not put numbers below the range you mean to sum.

T202301a.xlsm
	A	B	C
1		0.00	0.00
2	Account #	Period To Date	Year To Date
3	11010000	0.00	6,000.00
4	11020000	150,055.85	380,554.71
5	11201000	-20,824.02	-26,959.41
6	11202000	-8,427.34	85,710.18
7	11261000	0.00	54,204.96
8	11290000	-13,371.09	-58,120.10
9	11410000	169.69	22,494.76
10	11420001	312.97	2,721.01
11	11420002	-324.60	3,939.97
12	11420003	-374.78	10,723.55
13	11440000	556.44	2,011.11
14	11501000	-7,120.75	0.00
15	11505500	0.00	2,287.00
16	22016000	3,739.08	-28,957.31
17	22017000	-1,071.00	-12,520.00
18	22019000	-15,274.68	-34,150.03
19	22020000	-35,166.08	-121,700.45
20	22024000	0.00	-10,682.02
21	22101000	-8,668.22	-85,408.22
22	22103000	4,080.94	-929.94
23	22103500	0.00	-12,519.11
24	22105000	-31,462.40	-61,909.46
25	22106500	0.00	-16,016.20
26	22107000	-1,575.28	-42,927.67
27	22108000	-2,183.66	-2,183.66
28	22200000	-7,933.21	-13,636.78
29	22201000	-116,985.36	-190,500.00
30	22203000	-1,448.00	-11,569.00
31	22204000	-1,922.00	-37,276.00
32	22206001	587.20	-15,204.92
33	22206003	-9,087.11	-9,087.11
34	22210000	-13,745.80	-111,437.43
35	22310007	4,172.00	-5,280.00
36	22310020	33,134.94	0.00
37	22530000	0.00	555,771.53
38	22535000	0.00	-520,000.00
39	22537500	0.00	-116,900.00
40	22540000	0.00	650,077.50
41	103101000	-80,851.66	-1,134,433.80
42	103101500	-50,096.56	-1,018,318.54
43	103102000	-3,677.92	-63,028.44
44	103102500	-28,644.53	-292,215.55
45	103103000	-1,682.36	-20,855.77
46	103105500	-79,555.00	-113,387.25
47	103106000	0.00	-4,469.00
48	103106500	-38,620.00	-1,088,186.50
49	103108000	-356.89	-9,991.34
50	103109000	-2,786.01	-29,599.81
51	103109100	0.00	-242.00
52	103109200	0.00	-228.72
53	103109300	-58.86	-3,102.48
54	105000100	847.00	11,164.00
55	105000200	9,642.79	109,565.82
56	105105000	2,736.18	56,476.71
57	105105100	10,046.49	113,396.20
58	105108000	2,813.50	59,252.27
59	105108100	8,788.25	123,842.18
60	105108300	2,848.41	54,249.96
61	105108800	3,095.20	35,298.06
62	105949800	0.00	1,923.97
63	105949900	6,640.78	100,407.10
64	105951000	3,404.50	44,130.77
65	105951100	126.20	1,221.19
66	105951200	1,278.12	6,396.29
67	105952100	4,201.87	33,341.72
68	105953000	354.30	4,636.72
69	105953200	105.00	598.41
70	105953300	1,768.35	37,773.42
71	105953600	583.14	8,778.84
72	106016000	2,291.50	18,149.48
73	106020000	0.00	10,617.04
74	106022000	3,357.27	89,309.43
75	106022500	0.00	4,149.49
76	106022900	5,046.35	53,427.23
77	106023000	0.00	5,527.54
78	106024000	0.00	6,972.80
79	106028000	58.77	58.77
80	106036000	0.00	135.21
81	106048000	0.00	1,472.17
82	106049000	4,371.10	39,977.57
83	106050000	0.00	120.57
84	106059000	2,776.77	39,936.29
85	106063000	0.00	258.55
86	106066000	0.00	20,628.81
87	106072000	0.00	1,873.14
88	106074000	2,941.55	34,161.52
89	106075000	512.95	5,549.20
90	106084000	0.00	89.46
91	106089000	1,556.06	16,105.99
92	106100000	0.00	2,973.48
93	106102000	0.00	2,182.93
94	203200510	-2,732.87	-66,445.48
95	203200560	-375.00	-1,974.95
96	203200580	-4,410.00	-65,325.10
97	203201010	-9,278.15	-116,398.40
98	203201510	-7,855.15	-69,877.01
99	203201560	-174.50	-424.68
100	203201580	-4,885.34	-91,586.04
101	203202010	-17,235.88	-218,314.36
102	203202060	-854.54	-3,993.78
103	203202080	-41,908.61	-509,754.51
104	203202580	-18,367.40	-123,319.55
105	203203095	9.00	39.89
106	203205080	-836.10	-17,630.80
107	203206080	-5,975.00	-57,915.36
108	203208080	-23,405.41	-232,391.06
109	203209080	-150.00	-3,164.02
110	203300010	0.00	-123.20
111	203300060	0.00	12.00
112	203300080	0.00	-16,948.05
113	203301010	-14,127.33	-126,917.73
114	203301060	-22.00	-270.00
115	203301080	-3,682.00	-66,979.21
116	203302010	-9,670.06	-69,403.15
117	203302060	-35.00	-118.00
118	203302080	-3,848.00	-71,657.71
119	203303010	-31,521.39	-284,015.37
120	203303060	-10.00	-10.00
121	203303080	-4,080.00	-72,738.01
122	203304096	0.00	-20.23
123	204200000	49,862.63	430,993.76
124	204201000	320.00	15,266.84
125	204300000	518.34	21,652.34
126	204301000	3,451.94	31,185.44
127	204302000	2,479.38	28,856.58
128	204303000	5,445.18	61,002.76
129	205000100	1,184.00	15,208.00
130	205000200	20,194.99	184,136.62
131	205203000	9,524.81	75,791.46
132	205203200	4,447.71	66,375.38
133	205204010	5,476.50	122,798.13
134	205204070	2,815.93	44,246.85
135	205204110	5,667.56	58,022.85
136	205204170	2,297.53	21,884.54
137	205204210	-12.00	9,745.52
138	205204370	1,659.33	10,697.30
139	205205070	2,013.60	32,411.84
140	205207070	13,242.27	123,336.17
141	205301091	1,643.97	39,357.61
142	205949900	214.02	67,738.61
143	205951000	6,277.40	75,572.33
144	205951100	239.75	1,683.62
145	205951200	2,275.77	8,799.06
146	205952100	6,412.42	34,614.78
147	205953000	312.44	3,594.11
148	205953200	45.00	1,932.13
149	205953300	4,185.71	49,734.29
150	205953600	190.16	2,661.32
151	206014000	0.00	2,923.31
152	206019000	0.00	130.92
153	206020000	407.73	12,059.73
154	206023000	0.00	2,501.73
155	206024000	669.50	3,006.10
156	206028000	0.00	81.86
157	206030000	517.89	7,910.38
158	206036000	0.00	1,399.87
159	206038000	0.00	-274.61
160	206044000	30.09	1,135.14
161	206058000	793.01	5,433.65
162	206058500	360.81	5,960.24
163	206059000	0.00	478.00
164	206063000	0.00	6,014.41
165	206066000	6,709.84	54,676.50
166	206071000	854.70	12,126.72
167	206072000	0.00	59.60
168	206073000	0.00	808.60
169	206074000	1,495.72	13,306.07
170	206079000	2,754.84	31,064.55
171	206084000	0.00	966.83
172	206097000	0.00	604.80
173	206099000	0.00	272.12
174	206100000	0.00	3,815.52
175	206102000	1,125.82	11,850.11
176	206103000	555.13	14,402.19
177	483482000	-210.24	-9,723.95
178	483482001	0.00	-600.00
179	483482500	0.00	-11,124.00
180	483483500	-21.30	-228.22
181	483485000	-4,780.97	-27,728.67
182	483485500	0.00	8.00
183	483485600	0.00	-137.24
184	483487200	-295.47	-2,419.74
185	483487500	-3,748.32	-48,377.31
186	483488500	0.00	763.58
187	484487500	1,905.56	30,060.10
188	505000100	24,814.00	58,902.00
189	505000200	15,762.17	177,986.67
190	505503500	1,774.44	27,346.99
191	505951000	998.48	20,294.84
192	505951100	48.54	89.65
193	505951200	469.95	673.73
194	505952100	1,514.10	11,681.37
195	505953000	499.31	3,271.12
196	505953100	103.51	575.85
197	505953200	60.00	265.07
198	505953300	730.64	13,518.42
199	505953600	202.83	2,800.00
200	506013000	1,805.33	5,521.63
201	506017000	104.33	93.99
202	506018000	4,209.33	50,511.96
203	506023000	0.00	420.76
204	506025000	824.75	11,023.03
205	506027000	11,552.65	141,781.78
206	506031000	9.00	189.00
207	506036000	1,535.68	5,587.43
208	506051000	701.43	18,845.82
209	506062000	11.81	18,847.78
210	506063000	0.00	1,516.39
211	506067000	0.00	127.79
212	506072000	300.14	795.11
213	506074000	860.12	3,744.86
214	506080000	946.06	10,921.08
215	506083000	84.73	790.95
216	506087000	13,371.09	31,663.73
217	506087100	621.90	7,215.90
218	506091000	5,590.26	72,092.33
219	506097000	0.00	2,121.94
220	506099000	96.13	473.46
221	506100000	440.10	6,253.73
222	524521000	2,116.61	23,821.16
223	524522000	329.97	3,771.93
224	526024000	64.55	2,924.40
225	526025000	183.68	2,204.16
226	526521000	475.00	5,463.65
227	526521500	0.00	2,178.15
228	526522000	719.99	13,837.93
229	526522500	275.14	1,575.01
230	526523000	337.46	6,987.62
231	526523500	2,389.39	16,662.64
232	526524000	0.00	1,546.39
233	526524500	240.00	6,547.77
234	526525000	0.00	12,899.10
235	556551000	12,520.00	154,131.00
236	605000100	3,449.00	14,101.99
237	605000200	9,958.32	174,943.07
238	605608000	2,833.69	55,845.25
239	605951000	1,218.03	21,936.91
240	605951100	44.76	227.30
241	605951200	440.62	1,229.33
242	605952100	2,981.97	17,963.00
243	605953000	328.56	5,940.72
244	605953200	90.00	-395.32
245	605953300	588.55	15,305.85
246	605953600	152.12	2,104.86
247	606021010	159.32	784.84
248	606023000	0.00	937.31
249	606024000	1,950.00	13,285.48
250	606025000	2,960.01	35,905.90
251	606028000	0.00	21.35
252	606031000	406.30	10,391.09
253	606040010	11,461.14	151,956.08
254	606041010	8,595.88	113,967.90
255	606055010	0.00	4,145.15
256	606068010	9,374.85	112,414.85
257	606070010	800.00	14,407.97
258	606070011	971.71	12,088.54
259	606072000	0.00	500.00
260	606074000	312.09	6,383.08
261	606075010	0.00	1,295.00
262	606076000	0.00	960.00
263	606077010	0.00	10,047.70
264	606083000	0.00	77.64
265	606086000	0.00	2,718.05
266	606096000	0.00	3,368.81
267	606097000	272.07	679.08
268	606099000	0.00	762.58
269	606100000	0.00	5,102.94
270	606100500	0.00	2,725.00
271	705000100	560.00	7,720.00
272	705000200	5,068.38	61,976.77
273	705702000	3,390.59	42,558.07
274	705703000	3,667.00	44,151.74
275	705951000	1,088.78	12,940.14
276	705951100	27.05	228.05
277	705951200	396.16	1,273.90
278	705952100	1,335.45	11,253.99
279	705953000	172.62	2,139.31
280	705953200	75.00	271.14
281	705953300	786.80	10,525.45
282	705953600	139.45	1,899.18
283	706015000	520.51	13,754.91
284	706024000	1,077.56	47,117.44
285	706033000	224.61	3,107.78
286	706034000	1,092.00	16,722.12
287	706035000	81.22	10,762.55
288	706036000	0.00	472.37
289	706039000	128.60	405.18
290	706042000	0.00	2,403.40
291	706047000	1,328.53	20,765.30
292	706052000	0.00	7,713.80
293	706057000	0.00	461.87
294	706060000	13.69	7,788.37
295	706063000	0.00	1,653.75
296	706064000	0.00	8,570.32
297	706065000	0.00	4,860.88
298	706072000	0.00	1,880.78
299	706074000	330.55	3,960.09
300	706078000	0.00	2,169.91
301	706082000	0.00	9,719.99
302	706093000	0.00	4,341.01
303	706097000	0.00	300.00
304	706100000	0.00	8.05
305	706102000	498.90	7,392.35
306	706702500	1,767.81	18,803.92
307	756751000	14,271.09	140,525.26
308	756752000	13,001.66	76,989.76
309	756755000	3,977.15	20,603.89
310	756758500	42.85	524.47
311	806802500	0.00	3,888.11
312	806805000	116,985.36	444,863.66
313	806809000	7,120.75	60,368.78
314	915912000	1,335.70	21,012.00
315	915951000	129.72	1,857.94
316	915951100	6.43	43.27
317	915951200	57.44	219.87
318	915952100	523.40	1,794.44
319	915953200	-45.00	-210.00
320	915953300	109.24	1,140.91
321	916061000	659.84	13,427.02
322	916911000	-2,776.77	-39,285.45
323
2f

Cell Formulas
Range	Formula
B1:C1	B1	=SUM(B3:B500)

Yes, i agree. My issue is trying to come up with a formula that will sum up the numbers in B and C based on the account number that is 9 digits. There are account numbers that also have 8 digits so i want to be able to distinguish instead of always having to adjust the formula as the starting number will not always be the same.

 

[Fluff]

How about

=SUMIFS(B:B,A:A,">99999999")

 

[Dave Patton]

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)

 

[awoohaw]

why not make yoiur range a table, and then use this formula:
=SUMIFS(tbl_table[Year To Date],tbl_table[Account '#],">=" & 99999999)
where tbl_table is your table name.

 

[Fluff]

why not make yoiur range a table

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

 

[Katich]

How about

=SUMIFS(B:B,A:A,">99999999")

This worked! Thank you!