Sum Across Multiple Columns with date Critieria Across the header

[ChetanPuri]

Good Afternoon Excel Team,

I need help with summing across Columns using both dates and Customer Code criteria. As you can see on Excel query Sheet 1, I have columns with Date Headers and underneath I have data, which I want to sum in sheet Column E, is there a formula where I can match the date and customer code in Column B of sheet 2 with Column B of Sheet 1 and it summs it by matching the header data and Colmn A dates in Sheet2. Any help with the formula is much appreciated.

Many thanks,

Regards,
Chetan

 

[awoohaw]

Try this starting in cell E2 and copy down. You will need to adjust the Query Sheet 2 range references.

=LET(
Nbr,B2,
Dt,A2,
Data,'Query Sheet 2'!$O$8:$AA$105,
Dts,Sheet1!$O$8:$AA$8,
Nbrs,TAKE(Data,,2),
SUM(FILTER(FILTER(Data,Nbrs=Nbr,""),Dt=Dts,"")))

This was done free form. I did not recreate your data. If you'd like to have the forum use some of your data, please use the xl2bb add in or post your data in a Table that can be copied from the screen.

Thanks in advance.

 

[ChetanPuri]

Try this starting in cell E2 and copy down. You will need to adjust the Query Sheet 2 range references.

=LET(
Nbr,B2,
Dt,A2,
Data,'Query Sheet 2'!$O$8:$AA$105,
Dts,Sheet1!$O$8:$AA$8,
Nbrs,TAKE(Data,,2),
SUM(FILTER(FILTER(Data,Nbrs=Nbr,""),Dt=Dts,"")))

This was done free form. I did not recreate your data. If you'd like to have the forum use some of your data, please use the xl2bb add in or post your data in a Table that can be copied from the screen.

Thanks in advance.

Thank you Kindly, much appreciated.

 

[awoohaw]

Happy to help. Best Wishes.

 

[Peter_SSs]

Thank you Kindly, much appreciated.

Does that formula actually work for you? It doesn't for me.

 

[awoohaw]

This formula may work better:

=SUM(LET(
Nbr,B2,
Dt,A2,
Data,'Query Sheet 2'!$O$9:$AA$105,
Dts,'Query Sheet 2'!$O$8:$AA$8,
Nbrs,'Query Sheet 2'!$B$9:$B$105,
FILTER(FILTER(Data,Dts=Dt,""),Nbrs=Nbr,"")))

Or this one, i'm sure there are others, as well:

=LET(
Nbr,B2,
Dt,A2,
Data,'Query Sheet 2'!$O$9:$AA$105,
Dts,'Query Sheet 2'!$O$8:$AA$8,
Nbrs,'Query Sheet 2'!$B$9:$B$105,
SUM(Data*(Dts=Dt)*(Nbrs=Nbr)))

 

[ChetanPuri]

I will upload xlbb Tomorrow , as I just tried it giving an error

 

[Peter_SSs]

Given the apparent data structure in the images, I would go with a SUMIF

This is my sample data

ChetanPuri.xlsm
	B													O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
8														7/07/2023	14/07/2023	21/07/2023	28/07/2023	4/08/2023	11/08/2023	18/08/2023	25/08/2023	1/09/2023	8/09/2023	15/09/2023	22/09/2023	29/09/2023
9	a													4	4	3	6	3	8	4	4	6	1	4	4	4
10	a													2	3	2	9	9	1	3	4	7	9	3	8	2
11	b													8	6	7	8	5	7	3	1	4	6	1	3	8
12	c													4	5	2	6	9	1	3	2	1	9	5	9	7
13	a													5	6	7	9	1	6	6	3	3	7	8	2	5
14	c													8	1	8	6	8	3	8	6	3	7	3	4	7
15	b													2	2	4	3	2	1	1	6	2	9	8	8	5
16	c													6	3	1	5	6	5	9	6	4	7	5	4	7
17	a													6	3	2	1	4	1	3	7	5	2	9	4	3
Query Sheet 2

.. and results

ChetanPuri.xlsm
	A	B	C
1
2	7/07/2023	a	17
3	7/07/2023	b	10
4	7/07/2023	c	18
5	7/07/2023	d	0
6	14/07/2023	a	16
7	14/07/2023	c	9
8	14/07/2023	b	8
9	14/07/2023	d	0
10	1/09/2023	d	0
11	1/09/2023	a	21
12	1/09/2023	b	6
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=SUMIF('Query Sheet 2'!B$9:B$105,B2,INDEX('Query Sheet 2'!O$9:AA$105,0,MATCH(A2,'Query Sheet 2'!O$8:AA$8,0)))

 

[ChetanPuri]

Given the apparent data structure in the images, I would go with a SUMIF

This is my sample data

ChetanPuri.xlsm
	B													O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
8														7/07/2023	14/07/2023	21/07/2023	28/07/2023	4/08/2023	11/08/2023	18/08/2023	25/08/2023	1/09/2023	8/09/2023	15/09/2023	22/09/2023	29/09/2023
9	a													4	4	3	6	3	8	4	4	6	1	4	4	4
10	a													2	3	2	9	9	1	3	4	7	9	3	8	2
11	b													8	6	7	8	5	7	3	1	4	6	1	3	8
12	c													4	5	2	6	9	1	3	2	1	9	5	9	7
13	a													5	6	7	9	1	6	6	3	3	7	8	2	5
14	c													8	1	8	6	8	3	8	6	3	7	3	4	7
15	b													2	2	4	3	2	1	1	6	2	9	8	8	5
16	c													6	3	1	5	6	5	9	6	4	7	5	4	7
17	a													6	3	2	1	4	1	3	7	5	2	9	4	3
Query Sheet 2

.. and results

ChetanPuri.xlsm
	A	B	C
1
2	7/07/2023	a	17
3	7/07/2023	b	10
4	7/07/2023	c	18
5	7/07/2023	d	0
6	14/07/2023	a	16
7	14/07/2023	c	9
8	14/07/2023	b	8
9	14/07/2023	d	0
10	1/09/2023	d	0
11	1/09/2023	a	21
12	1/09/2023	b	6
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=SUMIF('Query Sheet 2'!B$9:B$105,B2,INDEX('Query Sheet 2'!O$9:AA$105,0,MATCH(A2,'Query Sheet 2'!O$8:AA$8,0)))

Thank you that worked:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Customer Code	7-Jul-23	14-Jul-23	21-Jul-23	28-Jul-23	4-Aug-23	11-Aug-23	18-Aug-23	25-Aug-23	1-Sep-23	8-Sep-23	15-Sep-23	22-Sep-23	29-Sep-23	6-Oct-23	13-Oct-23	20-Oct-23	27-Oct-23	3-Nov-23	10-Nov-23	17-Nov-23
2	100054	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18	2864.18
3	100054
4	100055	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42	2501.42
5	100056	5007.989	5007.989	5007.989	5007.989	5007.989	5007.9894	5007.9894	5007.9894	5007.989	5007.989	5007.9894	5007.9894	2861.7082
6	100056													2146.2812	5007.989	5007.989	5007.989	5007.989	5007.989	5007.9894	5007.9894
7	100057	3931.99	2808.564
8	100057		1123.426	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99	3931.99
9	100058	12570.25	12570.25	12570.25	12570.25	12570.25	12570.25	12570.25	12570.25	12570.25	12570.25	7183.0001
10	100058											5387.25	12570.25	12570.25	12570.25	12570.25	12570.25	12570.25	12570.25	12570.25	12570.25
11	100059	6203.59	6203.59	6203.59	6203.59	6203.59	6203.59	6203.59	6203.59	6203.59	6203.59	3544.9086
12	100059											2658.6814	6203.59	6203.59	6203.59	6203.59	6203.59	6203.59	6203.59	6203.59	6203.59
13	100060	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450	5450
14	100061	7101.6	7101.6	7101.6	7101.6	7101.6	7101.6	7101.6	7101.6	7101.6	7101.6	7101.6	5072.5714
15	100061												2029.0286	7101.6	7101.6	7101.6	7101.6	7101.6	7101.6	7101.6	7101.6
16	100062	7963.52	7963.52	7963.52	7963.52	7963.52	7963.5199	7963.5199	7963.5199	7963.52	7963.52	7963.5199	7963.5199	7963.5199	7963.52	3412.937
17	100062															4531.749	7930.56	7930.56	7930.56	7930.56	7930.56
18	100063	5870.066	5870.066	5870.066	5870.066	5870.066	5870.0658	5870.0658	5870.0658	5870.066	5870.066	5870.0658	5870.0658	4192.9041
19	100063													1711.9341	5991.769	5991.769	5991.769	5991.769	5991.769	5991.7695	5991.7695
20	100063
21	100064	3906.65	3906.65	3906.65	3906.65	3348.557
22	100064					526.9586	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71	3688.71
23	100065	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74	5450.74
24	100065
25	100066	8570.38	4897.36
26	100066		4800.269	11200.63	11200.63	11200.63	11200.627	11200.627	11200.627	11200.63	11200.63	11200.627	11200.627	11200.627	11200.63	11200.63	11200.63	11200.63	11200.63	11200.627	11200.627
27	100066
28	100067	9297.19	9297.19	9297.19	9297.19	9297.19	9297.1901	9297.1901	9297.1901	9297.19	9297.19	9297.1901	9297.1901	9297.1901	9297.19	9297.19	9297.19	9297.19	9297.19	9297.1901	9297.1901
29	100068	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93	10604.93
30	100069	4894.059	4894.059	4894.059	4894.059	4894.059	4894.0588	4894.0588	4894.0588	4894.059	4894.059	4894.0588	4894.0588	4894.0588	4894.059	4894.059	4894.059	4894.059	4894.059	4894.0588	4894.0588
31	100070	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873	6771.873
32	100070																				7186.27
33	100071	7965.811	7965.811	7965.811	7965.811	7965.811	4551.8921
34	100071						3413.9191	7965.8112	7965.8112	7965.811	7965.811	7965.8112	7965.8112	7965.8112	7965.811	7965.811	7965.811	7965.811	7965.811	7965.8112	7965.8112
35	100072	6510.34	6510.34	6510.34	6510.34	6510.34	6510.34	6510.34	6510.34
36	100072									6981.1	6981.1	6981.0999	6981.0999	6981.0999	6981.1	6981.1	6981.1	6981.1	6981.1	6981.0999	6981.0999
37	100073
38	100073	4860.02	4860.02	4860.02	4860.02	4860.02	4860.0199	4860.0199	4860.0199	4860.02	4860.02	4860.0199	4860.0199	4860.0199	4860.02	4860.02	4860.02	4860.02	4860.02	4860.0199	4860.0199
39	100074
40	100074		3490.98	3490.98	3490.98	3490.98	3490.9801
41	100074							3638.14	3638.14	3638.14	3638.14	3638.14	3638.14	3638.14	3638.14	3638.14	3638.14	3638.14	3638.14
42	100074																			7384.79	7384.79
43	100075	4631.57	4631.57	4631.57	4631.57	1984.959
44	100075					2663.914	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85	4661.85
45	100076	5175.57	5175.57	5175.57	5175.57	5175.57	5175.57	5175.57	5175.57	5175.57	5175.57	5175.57	5175.57	2957.4686
46	100076													2219.4986	5178.83	5178.83	5178.83	5178.83	5178.83	5178.83	5178.83
47	100077	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35	4794.35
48	100078	4188.603	4188.603	4188.603	4188.603	4188.603	4188.6033	598.3719
49	100078								4411.8012	3151.287
50	100078									1260.514	4411.8	4411.8	4411.8	4411.8	4411.8	4411.8	4411.8	4411.8	4411.8	4411.8	4411.8
51	100079	6660.95	6660.95	6660.95	6660.95	6660.95	6660.9499	6660.9499	6660.9499	6660.95	6660.95	6660.9499	6660.9499	6660.9499	6660.95	6660.95	6660.95	6660.95	6660.95	6660.9499	6660.9499
52	100081	4728.553	4728.553	4728.553	4728.553
53	100081					4917.52	4917.5199	4917.5199	4917.5199	4917.52	4917.52	4917.5199	4917.5199	4917.5199	4917.52	4917.52	4917.52	4917.52	4917.52	4917.5199	4917.5199
54	100082	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71	4210.71
55	100083
56	100083	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41	4216.41
57	100084	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02	9980.02
58	100085	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99	5098.99
59	100086	6411.83	6411.83	6411.83	6411.83	6411.83	6411.8298	6411.8298	6411.8298	6411.83	6411.83	6411.8298	6411.8298	6411.8298	6411.83	6411.83	6411.83	6411.83	6411.83	6411.8298	6411.8298
60	100087	6657.267	6657.267	6657.267	6657.267	6657.267	6657.2666	6657.2666	6657.2666	6657.267	6657.267	6657.2666	6657.2666	6657.2666	6657.267	6657.267	6657.267	6657.267	6657.267	6657.2666
61	100087																				7176.65
62	100088	7021.181	7021.181	7021.181	7021.181	7021.181	7021.1806	7021.1806	7021.1806	7021.181	7021.181	7021.1806	7021.1806	7021.1806	7021.181	7021.181	7021.181	7021.181	7021.181	7021.1806	7021.1806
63	100089	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03	5889.03
64	100091	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41	6433.41
65	100092	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55	6522.55
66	100093	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086	5086
67	100094	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14	8866.14
68	100095	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78	4632.78
69	100096	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75	4926.75
70	100098	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58	4186.58
71	100099	6232.076	6232.076	6232.076	6232.076	6232.076	6232.0762	6232.0762	6232.0762	6232.076	6232.076	6232.0762	6232.0762	6232.0762	6232.076	6232.076	6232.076	6232.076	6232.076	6232.0762	6232.0762
72	100099
73	100100
74	100100	11474.21
75	100100	3291.209	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23	11519.23
76	100172
77	100172	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72	13577.72
78	100185	9310.1	9310.1	9310.1	9310.1	9310.1	9310.0999	9310.0999	9310.0999	9310.1	9310.1	9310.0999	9310.0999	9310.0999	9310.1	9310.1	9310.1	9310.1	9310.1	9310.0999	9310.0999
79	100194	5500.979	5500.979	5500.979	5500.979	5500.979	5500.9788	5500.9788	5500.9788	5500.979	5500.979	5500.9788	5500.9788	5500.9788	5500.979	5500.979	5500.979	5500.979	5500.979	5500.9788	5500.9788
80	100229	4632.87	4632.87	4632.87	4632.87	4632.87	4632.87	4632.87	4632.87	4632.87
81	100229										4632.87	4632.87	4632.87	4632.87	4632.87	4632.87	4632.87	4632.87	4632.87	4632.87	4632.87
82	100239
83	100239
84	100239	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81	10768.81
85	100245	9268.6	9268.6	9268.6	9268.6	9268.6	9268.5999	9268.5999	9268.5999	9268.6	9268.6	9268.5999	9268.5999	9268.5999	9268.6	9268.6	9268.6	9268.6	9268.6	9268.5999	9268.5999
86	100246	11415.37	11415.37	11415.37	11415.37	11415.37	11415.37	11415.37	9784.6029
87	100246								1717.1971	12020.38	12020.38	12020.38	12020.38	12020.38	12020.38	12020.38	12020.38	12020.38	12020.38	12020.38	12020.38
88	100247
89	100247	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69	11583.69
90	100267	8173.16	8173.16	8173.16	4670.377
91	100267				3502.783	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16	8173.16
92	100268	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33	8936.33
93	100269	2488.553	2488.553	2488.553	2488.553	2488.553	2488.5532	2488.5532	2488.5532	2488.553	2488.553	2488.5532	2488.5532	2488.5532	2488.553	2488.553	2488.553	2488.553	2488.553	2488.5532	2488.5532
94	100270
95	100270	10972.46	10972.46	10972.46	10972.46	10972.46	10972.46	10972.46	10972.46
96	100270									12715.01	12715.01	12715.009	12715.009	12715.009	12715.01	12715.01	12715.01	12715.01	12715.01	12715.009	12715.009
97	100271	5933.517	5933.517	5933.517	5933.517	5933.517	5933.5169	5933.5169	5933.5169	5933.517	5933.517	5933.5169	5933.5169	5933.5169	5933.517	5933.517	5933.517	5933.517	5933.517	5933.5169	5933.5169
98	100271
99	100272	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	12232.26	10484.79
100	100272																1703.957	11927.7	11927.7	11927.7	11927.7
101	100273	5535.989	5535.989	5535.989	5535.989	5535.989	5535.9887	5535.9887	5535.9887	5535.989	5535.989	5535.9887	5535.9887	5535.9887	3954.278
102	100273														1642.831	5749.91	5749.91	5749.91	5749.91	5749.91	5749.91
103	100273
104	100322	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35	6275.35
105	100322
106	100325	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9	11314.9
107	100328	10695.53	10695.53	10695.53	10695.53	10695.53	10695.53	10695.53	10695.53	10695.53	10695.53	10695.53	10695.53	10695.53	10695.53	9167.597
108	100328															1575.926	11031.48	11031.48	11031.48	11031.48	11031.48
109	100358
110	100358	9963.97	9963.97	9963.97	9963.97	9963.97	9963.9699	9963.9699	9963.9699	9963.97	9963.97	9963.9699	9963.9699	9963.9699	9963.97	9963.97	9963.97	9963.97	9963.97	9963.9699	9963.9699
111	100359	11016.63	11016.63	11016.63	11016.63	11016.63	11016.63	11016.63	11016.63	11016.63	11016.63	11016.63	11016.63	11016.63	4721.413
112	100359														6640.331	11620.58	11620.58	11620.58	11620.58	11620.58	11620.58
113	100363	28201.06	28201.06	28201.06	28201.06	28201.06	28201.06
114	100363							29641.88	29641.88	29641.88	29641.88	29641.88	29641.88	29641.88	29641.88	29641.88	29641.88	29641.88	29641.88	29641.88	29641.88
115	100380	6780	6780	6780	6780	6780	6780	6780	6780	6780	6780	2905.7143
116	100380											4004.6057	7008.06	7008.06	7008.06	7008.06	7008.06	7008.06	7008.06	6006.9086
117	100380																			969.31571	6785.21
118	100396							2963.1234	6913.9546	6913.955	6913.955	6913.9546	6913.9546	6913.9546	6913.955	6913.955	6913.955	6913.955	6913.955	6913.9546	6913.9546
119	100397							2963.1234	6913.9546	6913.955	6913.955	6913.9546	6913.9546	6913.9546	6913.955	6913.955	6913.955	6913.955	6913.955	6913.9546	6913.9546
120	100397
121	100420																3852.09	8988.21	8988.21	8988.21	8988.21
122	100420
123	100433
124		484191.2	485563.2	487066.2	487066.2	487241.4	487067.53	490991.52	502793.05	505524.9	505524.9	505655.25	505752.99	505789.16	506284.2	506725	510807.4	515682.5	515682.5	519397.28	520140.05
Query Sheet 2

Cell Formulas
Range	Formula
B124:U124	B124	=SUM(B2:B123)

Book3
	A	B	C	D	E
1	Date	Customer Code	AR	GL	Query Sheet 2
2	7-Jul-23	100054	2,864.18	2,864.18	2,864.18
3	7-Jul-23	100055	2,633.57	2,633.57	2,501.42
4	7-Jul-23	100056	5,008.00	5,008.00	5,007.99
5	7-Jul-23	100057	3,714.73	3,714.73	3,931.99
6	7-Jul-23	100058	12,592.45	12,592.45	12,570.25
7	7-Jul-23	100059	6,334.26	6,334.26	6,203.59
8	7-Jul-23	100060	5,449.28	5,449.28	5,450.00
9	7-Jul-23	100061	6,210.11	6,210.11	7,101.60
10	7-Jul-23	100062	8,383.65	8,383.65	7,963.52
11	7-Jul-23	100063	6,124.73	6,124.73	5,870.07
12	7-Jul-23	100064	3,292.98	3,292.98	3,906.65
13	7-Jul-23	100065	5,739.63	5,739.63	5,450.74
14	7-Jul-23	100066	8,961.57	8,961.57	9,697.63
15	7-Jul-23	100067	9,789.94	9,789.94	9,297.19
16	7-Jul-23	100068	10,483.05	10,483.05	10,604.93
17	7-Jul-23	100069	4,052.17	4,052.17	4,894.06
18	7-Jul-23	100070	7,186.27	7,186.27	6,771.87
19	7-Jul-23	100071	8,386.04	8,386.04	7,965.81
20	7-Jul-23	100072	6,510.35	6,510.35	6,510.34
21	7-Jul-23	100073	4,860.02	4,860.02	4,860.02
22	7-Jul-23	100074	3,676.00	3,676.00	3,490.98
23	7-Jul-23	100075	4,877.04	4,877.04	4,631.57
24	7-Jul-23	100076	5,175.57	5,175.57	5,175.57
25	7-Jul-23	100077	4,794.36	4,794.36	4,794.35
26	7-Jul-23	100078	4,437.01	4,437.01	4,188.60
27	7-Jul-23	100079	6,254.79	6,254.79	6,660.95
28	7-Jul-23	100081	5,039.15	5,039.15	4,728.55
29	7-Jul-23	100082	4,210.71	4,210.71	4,210.71
30	7-Jul-23	100083	4,226.16	4,226.16	4,216.41
31	7-Jul-23	100084	10,508.96	10,508.96	9,980.02
32	7-Jul-23	100085	5,369.24	5,369.24	5,098.99
33	7-Jul-23	100086	6,751.66	6,751.66	6,411.83
34	7-Jul-23	100087	6,675.19	6,675.19	6,657.27
35	7-Jul-23	100088	0.00	7,393.30	7,021.18
36	7-Jul-23	100089	5,889.03	5,889.03	5,889.03
37	7-Jul-23	100091	6,638.44	6,638.44	6,433.41
38	7-Jul-23	100092	6,868.25	6,868.25	6,522.55
39	7-Jul-23	100093	5,014.39	5,014.39	5,086.00
Query 1

Cell Formulas
Range	Formula
E2:E39	E2	=IFERROR(SUMIF('Query Sheet 2'!$A$2:$A$3000,'Query 1'!$B$2:$B$3000,INDEX('Query Sheet 2'!$B$2:$BA$123,0,MATCH('Query 1'!$A$2:$A$3000,'Query Sheet 2'!$1:$1,0)))," ")

 

[Peter_SSs]

Thank you that worked:

Good news.

However, I don't see how Query 1 column E in your mini sheet is showing the results it is as the formula shown for that column is incorrect.
I think that part of the confusion with any checking that you have done is caused by the fact that you have so many identical values across a row in Query Sheet 2

=IFERROR(SUMIF('Query Sheet 2'!$A$2:$A$3000,'Query 1'!$B$2:$B$3000,INDEX('Query Sheet 2'!$B$2:$BA$123,0,MATCH('Query 1'!$A$2:$A$3000,'Query Sheet 2'!$1:$1,0)))," ")

SUMIF should have ranges that are the same size but those two red ranges are very different in size (number of rows).
Also your MATCH function is looking at the blue range so will find the column with the correct date. Suppose that match returns a '3' you would then be indexing column 3 of a range starting at column B (the second red one above). Column 3 of that red range is actually column 4 on the worksheet which would not be the column with the matching date.
An example of this with your data/formula is code 100074 for 7-Jul-23. Your results sheet shows 3490.98 but if you look at Query Sheet 2 for that code/date all the cells are blank meaning the result should be 0.

I would also highly recommend not using space characters for the IFERROR alternative and not using the sheet name that the formula is on (Query 1) within the formula as that can lead to incorrect results in some circumstances.

I believe that your formula for A2 should be this, copied down
=IFERROR(SUMIF('Query Sheet 2'!$A$2:$A$3000,B2,INDEX('Query Sheet 2'!$A$2:$BA$3000,0,MATCH(A2,'Query Sheet 2'!$1:$1,0))),"")