VLOOKUP? or another formula?

[kimberlie3]

I have a spreadsheet that I am trying to automate to make things easier. This is a spreadsheet that will have additional rows added that I am looking to auto populate. This is what I am doing:

1. populating a column with pretax amount by using formula (matching-sales tax)

2. Then previously I was using formulas to get the total spent for each FCN/Line but would manually have to go in and use the sum formula for each FCN/Line

Is there a way I can make this simple? I attached a photo of an example and what I am looking to do. The green section is what it would look like if I did it manually, but after a few months, I usually have a few hundred rows or more and can be time consuming doing it manually.

Any help would be greatly appreciated!!

 

[Cubist]

Try this in E25:

=SUMIFS($G$3:$G$18,$D$3:$D$18,B25,$E$3:$E$18,C25)

 

[kimberlie3]

Try this in E25:

=SUMIFS($G$3:$G$18,$D$3:$D$18,B25,$E$3:$E$18,C25)

it only worked for rows E32-43, the E25-31 all populated as 0s where E25 should be 1505.42, E26 164.50 and so on....

 

[Cubist]

The 0's suggest that there are no matches found. The common cause is that there are extra spaces in the cells and you can't visibly see. Can you post your data in a copy-able format for testing? Preferably using XL2BB.

 

[kimberlie3]

2024-2025 Budgets.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Transaction Amount	Sales Tax Amount (GST)	Matching Amount	FCN	Line	G/L	Pre-tax
3	367.37	354.99	367.37	400098230	6	65008885	12.38
4	3,945.20	493.79	3,795.70	400098230	7	63003010	3,301.91
5	3,945.20	19.50	149.50	400098230	2	65400000	130.00
6	282.19	36.81	282.19	400098230	12	65300055	245.38
7	91.98	12.00	91.98	400098230	11	65300020	79.98
8	278.86	36.37	278.86	400098230	1	65200200	242.49
9	139.09	1.05	8.04	400098230	1	65200200	6.99
10	288.06	37.57	288.06	400098230	3	65200210	250.49
11	1,245.45	0.00	34.50	400098230	2	65400000	34.50
12	1,245.45	0.00	1,210.95	400098230	1	65200200	1,210.95
13	17.19	2.24	17.19	400098230	4	65002090	14.95
14	51.74	6.75	51.74	400098230	1	65200200	44.99
15	245.00	0.00	245.00	400105412	2	65009020	245.00
16	105.00	0.00	105.00	400105412	1	65009030	105.00
17	219.42	6.34	156.03	400103623	11	65000000	149.69
18	1,839.99	240.00	1,839.99	400106371	1	65300015	1,599.99
19
20
21
22	Commitments
23
24	G/L	FCN	Line Number	SO#	Total Spent		Example	G/L	FCN	Line Number	SO#	Total Spent
25	65200200	400098230	1	7	0			65200200	400098230	1	7	1,505.42
26	65400000	400098230	2	R021	0			65400000	400098230	2	R021	164.50
27	65200210	400098230	3	7	0			65200210	400098230	3	7	250.49
28	65002090	400098230	4	3	0			65002090	400098230	4	3	14.95
29	65200165	400098230	5	7	0			65200165	400098230	5	7
30	65008885	400098230	6	4	0			65008885	400098230	6	4	12.38
31	63003010	400098230	7	6	0			63003010	400098230	7	6	3,301.91
32	65005030	400098230	8	5	0			65005030	400098230	8	5
33	65200041	400098230	9	R112A	0			65200041	400098230	9	R112A
34	65200190	400098230	10	7	0			65200190	400098230	10	7
35	65300020	400098230	11	9	79.98			65300020	400098230	11	9	79.98
36	65300055	400098230	12	9	245.38			65300055	400098230	12	9	245.38
37	65200180	400098230	13	7	0			65200180	400098230	13	7
38	63003010	400098230	14	6	0			63003010	400098230	14	6
39	65009030	400105412	1	4	105			65009030	400105412	1	4	105.00
40	65009020	400105412	2	4	245			65009020	400105412	2	4	245.00
41	65008635	400106136	1	4	0			65008635	400106136	1	4
42	65300015	400106371	1	9	1599.99			65300015	400106371	1	9	1,599.99
43	65000000	400103623	11	R021	149.69			65000000	400103623	11	R021	149.69
44
Sheet9

Cell Formulas
Range	Formula
G3:G18	G3	=C3-SUM(B3:B3)
L25	L25	=SUM(G8+G9+G12+G14)
L26	L26	=G11+G5
L27	L27	=G10
L28	L28	=G13
L30:L31	L30	=G3
L35	L35	=G7
L36	L36	=G6
L39	L39	=G16
L40	L40	=G15
E25:E43	E25	=SUMIFS($G$3:$G$18,$D$3:$D$18,B25,$E$3:$E$18,C25)
L42	L42	=G18
L43	L43	=G17

 

[Cubist]

When I paste your mini-sheet into my workbook, it works fine so I don't have a clue why it's not on yours.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Transaction Amount	Sales Tax Amount (GST)	Matching Amount	FCN	Line	G/L	Pre-tax
3	367.37	354.99	367.37	400098230	6	65008885	12.38
4	3945.2	493.79	3795.7	400098230	7	63003010	3301.91
5	3945.2	19.5	149.5	400098230	2	65400000	130
6	282.19	36.81	282.19	400098230	12	65300055	245.38
7	91.98	12	91.98	400098230	11	65300020	79.98
8	278.86	36.37	278.86	400098230	1	65200200	242.49
9	139.09	1.05	8.04	400098230	1	65200200	6.99
10	288.06	37.57	288.06	400098230	3	65200210	250.49
11	1245.45	0	34.5	400098230	2	65400000	34.5
12	1245.45	0	1210.95	400098230	1	65200200	1210.95
13	17.19	2.24	17.19	400098230	4	65002090	14.95
14	51.74	6.75	51.74	400098230	1	65200200	44.99
15	245	0	245	400105412	2	65009020	245
16	105	0	105	400105412	1	65009030	105
17	219.42	6.34	156.03	400103623	11	65000000	149.69
18	1839.99	240	1839.99	400106371	1	65300015	1599.99
19
20
21
22	Commitments
23
24	G/L	FCN	Line Number	SO#	Total Spent		Example	G/L	FCN	Line Number	SO#	Total Spent
25	65200200	400098230	1	7	1505.42			65200200	400098230	1	7	1505.42
26	65400000	400098230	2	R021	164.5			65400000	400098230	2	R021	164.5
27	65200210	400098230	3	7	250.49			65200210	400098230	3	7	250.49
28	65002090	400098230	4	3	14.95			65002090	400098230	4	3	14.95
29	65200165	400098230	5	7	0			65200165	400098230	5	7
30	65008885	400098230	6	4	12.38			65008885	400098230	6	4	12.38
31	63003010	400098230	7	6	3301.91			63003010	400098230	7	6	3301.91
32	65005030	400098230	8	5	0			65005030	400098230	8	5
33	65200041	400098230	9	R112A	0			65200041	400098230	9	R112A
34	65200190	400098230	10	7	0			65200190	400098230	10	7
35	65300020	400098230	11	9	79.98			65300020	400098230	11	9	79.98
36	65300055	400098230	12	9	245.38			65300055	400098230	12	9	245.38
37	65200180	400098230	13	7	0			65200180	400098230	13	7
38	63003010	400098230	14	6	0			63003010	400098230	14	6
39	65009030	400105412	1	4	105			65009030	400105412	1	4	105
40	65009020	400105412	2	4	245			65009020	400105412	2	4	245
41	65008635	400106136	1	4	0			65008635	400106136	1	4
42	65300015	400106371	1	9	1599.99			65300015	400106371	1	9	1599.99
43	65000000	400103623	11	R021	149.69			65000000	400103623	11	R021	149.69
44
Sheet7

Cell Formulas
Range	Formula
G3:G18	G3	=C3-SUM(B3:B3)
L25	L25	=SUM(G8+G9+G12+G14)
L26	L26	=G11+G5
L27	L27	=G10
L28	L28	=G13
L30:L31	L30	=G3
L35	L35	=G7
L36	L36	=G6
L39	L39	=G16
L40	L40	=G15
E25:E43	E25	=SUMIFS($G$3:$G$18,$D$3:$D$18,B25,$E$3:$E$18,C25)
L42	L42	=G18
L43	L43	=G17

 

[kimberlie3]

That is so strange! I also tried the formula in other sheets and getting the same issue but if I copy your sheet, it shows the correct sums....could it be a cell format issue do you think?

 

[Cubist]

In some convenient blank cells,say I3 and J3, try this

=LEN(D3:D18)=LEN(TRIM(D3:D18))

and

=LEN(E3:E18)=LEN(TRIM(E3:E18))

What do they return?

 

[kimberlie3]

 

[Cubist]

Ok, that's expected.
Try to do the same formulas but for B and C 25-43.
Then check:

=B25=D8

and

=C25=E8