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

 

[ChetanPuri]

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))),"")

Initially I had the incorrect formula, but had fixed it since:

SIL Revenue Review 16 Feb 2024.xlsx
	A	B	C	D	E
1	Date	Customer Code	AR	GL	WorkSheet
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	8,570.38
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	0.00
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
40	7-Jul-23	100094	6,172.33	6,172.33	8,866.14
41	7-Jul-23	100095	4,878.32	4,878.32	4,632.78
42	7-Jul-23	100096	5,230.49	5,230.49	4,926.75
43	7-Jul-23	100098	4,408.47	4,408.47	4,186.58
44	7-Jul-23	100099	6,562.37	6,562.37	6,232.08
45	7-Jul-23	100100	16,915.29	16,915.29	14,765.42
46	7-Jul-23	100172	14,297.34	14,297.34	13,577.72
47	7-Jul-23	100185	9,803.54	9,803.54	9,310.10
48	7-Jul-23	100194	5,792.53	5,792.53	5,500.98
49	7-Jul-23	100229	4,136.61	4,136.61	4,632.87
50	7-Jul-23	100239	10,009.03	10,009.03	10,768.81
51	7-Jul-23	100245	9,295.58	9,295.58	9,268.60
52	7-Jul-23	100246	12,020.38	12,020.38	11,415.37
53	7-Jul-23	100247	12,230.47	12,230.47	11,583.69
3 Way Rec

Cell Formulas
Range	Formula
C2:C53	C2	=SUMIFS(Data!$G:$G,Data!$B:$B,'3 Way Rec'!$B:$B,Data!$A:$A,">="&'3 Way Rec'!$A:$A,Data!$A:$A,"<="&'3 Way Rec'!$A:$A)
D2:D53	D2	=-SUMIFS(Revenue!$G:$G,Revenue!$J:$J,'3 Way Rec'!$B:$B,Revenue!$B:$B,">="&'3 Way Rec'!$A:$A,Revenue!$B:$B,"<="&'3 Way Rec'!$A:$A)
E2:E53	E2	=IFERROR(SUMIF('SIL July 2023-2024'!$B$9:$B$130,'3 Way Rec'!$B$2:$B$2249,INDEX('SIL July 2023-2024'!$O$9:$BN$130,0,MATCH('3 Way Rec'!$A$2:$A$2249,'SIL July 2023-2024'!$O$8:$BN$8,0))),"0")

Named Ranges
Name	Refers To	Cells
Data!ExternalData_1	=Data!$A$1:$L$2602	C2:C53

 

[Peter_SSs]

That still looks wrong/risky to me

• It still contains the worksheet name of the worksheet that it is on ('3 Way Rec'). As I stated before, that can lead to incorrect results in some circumstances and you wouldn't necessarily know what, if anything, was incorrect. There is no need for that name to be in the formula.
  
  
• Your profile shows Excel 365 but are you actually using this workbook in 365?
  
  
• In cell E2 your formula contains '3 way Rec'!$B$2:$B$2249 and '3 way Rec'!$A$2:$A$2249. Apart from the sheet names that shouldn't be there, why do you have those whole ranges there? Isn't the formula in E2 only looking at the code in B2 and the date in A2? Compare to my formula structure in post 10 and post 8 where I have just used B2 and A2.

 

[ChetanPuri]

That still looks wrong/risky to me

• It still contains the worksheet name of the worksheet that it is on ('3 Way Rec'). As I stated before, that can lead to incorrect results in some circumstances and you wouldn't necessarily know what, if anything, was incorrect. There is no need for that name to be in the formula.
  
  
• Your profile shows Excel 365 but are you actually using this workbook in 365?
  
  
• In cell E2 your formula contains '3 way Rec'!$B$2:$B$2249 and '3 way Rec'!$A$2:$A$2249. Apart from the sheet names that shouldn't be there, why do you have those whole ranges there? Isn't the formula in E2 only looking at the code in B2 and the date in A2? Compare to my formula structure in post 10 and post 8 where I have just used B2 and A2.

Thank you, I got it instead of giving it a range I should only refer to A2 & B2 as and when I add more data at the Bottom it shouldn't limit to the range?