Corralating data from seperate column

[EliteBeat]

Hi there,

Basically I've got a spreadsheet with 2 tabs. The first is a list of queries created for various restaurants, each of these restaurants has a number. The second tab has the restaurant number alongside a divison that it's a part of. How can I find out the total queries created by division. I'm stumped on how to connect the correct division back to the corresponding restaurant on the queries tab. Thanks

excel file.xlsx
	A	B	C	D
1	Number	Created	Month Created	Restaurant Number
5	PQS1160998	13-05-21 17:18	May	1001
6	PQS1160971	13-05-21 17:01	May	1001
9	PQS1165398	18-05-21 11:06	May	1001
11	PQS1172770	25-05-21 17:46	May	1001
12	PQS1172770	25-05-21 17:46	May	1001
13	PQS1179289	03-06-21 09:49	June	1001
14	PQS1185835	09-06-21 14:51	June	1001
20	PQS1188138	11-06-21 22:10	June	1001
23	PQS1188138	11-06-21 22:10	June	1001
24	PQS1194371	20-06-21 15:18	June	1001
31	PQS1185829	09-06-21 14:48	June	1001
32	PQS1149827	27-04-21 16:05	April	1002
33	PQS1161534	14-05-21 11:50	May	1002
34	PQS1171612	24-05-21 17:13	May	1002
35	PQS1171572	24-05-21 16:45	May	1002
36	PQS1177527	01-06-21 14:41	June	1002
37	PQS1178936	02-06-21 16:49	June	1002
39	PQS1196364	22-06-21 14:22	June	1002
40	PQS1197316	23-06-21 14:08	June	1002
41	PQS1214507	15-07-21 10:45	July	1002
48	PQS1190034	14-06-21 16:56	June	1002
52	PQS1176776	31-05-21 21:11	May	1003
61	PQS1185619	09-06-21 12:31	June	1003
66	PQS1197597	23-06-21 18:37	June	1003
67	PQS1152077	03-05-21 11:44	May	1004
68	PQS1155104	06-05-21 22:48	May	1004
70	PQS1158147	11-05-21 11:54	May	1004
71	PQS1163046	16-05-21 18:00	May	1004
72	PQS1167990	20-05-21 13:23	May	1004
75	PQS1168143	20-05-21 14:58	May	1004
78	PQS1171758	24-05-21 20:35	May	1004
80	PQS1205867	03-07-21 23:01	July	1004
81	PQS1212814	13-07-21 10:51	July	1004
82	PQS1163755	17-05-21 11:44	May	1005
83	PQS1172933	26-05-21 08:22	May	1005
85	PQS1171696	24-05-21 18:51	May	1005
90	PQS1170126	23-05-21 19:43	May	1005
Queries

Cell Formulas
Range	Formula
C90,C85,C80:C83,C78,C75,C70:C72,C66:C68,C61,C52,C48,C39:C41,C31:C37,C23:C24,C20,C11:C14,C9,C5:C6	C5	=TEXT(MONTH(B5)*29,"Mmmm")

excel file.xlsx
	A	B	C
1	restaurant No	restaurant Name	Division
2	1001	restaurant 1	Divison A
3	1002	restaurant 2	Division B
4	1003	restaurant 3	Division C
5	1004	restaurant 4	Division C
6	1005	restaurant 5	Divison A
7	1006	restaurant 6	Division B
8	1007	restaurant 7	Division C
9	1008	restaurant 8	Divison A
10	1009	restaurant 9	Division B
11	1010	restaurant 10	Division C
12	1011	restaurant 11	Divison A
13	1012	restaurant 12	Division B
14	1013	restaurant 13	Division C
15	1014	restaurant 14	Division B
16	1015	restaurant 15	Division C
17	1016	restaurant 16	Division C
18	1017	restaurant 17	Divison A
19	1018	restaurant 18	Division B
20	1019	restaurant 19	Division C
21	1020	restaurant 20	Division C
22	1021	restaurant 21	Division C
23	1022	restaurant 22	Divison A
restaurant

 

[GraH]

Isn't it simply

=COUNTIF(Queries!D:D;A2)

Then put a pivot on division? Or again a table with the divisions, using a sumif?

EDIT, or even more simply, fetch the divisions first, then do a simply countif.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Number	Created	Month Created	Restaurant Number	Div					restaurant No	restaurant Name	Division	Total Queries
2	PQS1149827	27/04/2021 16:05	April	1002	Division B		Row Labels	Sum of Total Queries		1001	restaurant 1	Division A	11
3	PQS1152077	3/05/2021 11:44	May	1004	Division C		Division B	10		1002	restaurant 2	Division B	10
4	PQS1155104	6/05/2021 22:48	May	1004	Division C		Division C	12		1003	restaurant 3	Division C	3
5	PQS1158147	11/05/2021 11:54	May	1004	Division C		Divison A	15		1004	restaurant 4	Division C	9
6	PQS1160971	13/05/2021 17:01	May	1001	Division A		Grand Total	37		1005	restaurant 5	Division A	4
7	PQS1160998	13/05/2021 17:18	May	1001	Division A					1006	restaurant 6	Division B	0
8	PQS1161534	14/05/2021 11:50	May	1002	Division B		Division A	15		1007	restaurant 7	Division C	0
9	PQS1163046	16/05/2021 18:00	May	1004	Division C		Division B	10		1008	restaurant 8	Division A	0
10	PQS1163755	17/05/2021 11:44	May	1005	Division A		Division C	12		1009	restaurant 9	Division B	0
11	PQS1165398	18/05/2021 11:06	May	1001	Division A					1010	restaurant 10	Division C	0
12	PQS1167990	20/05/2021 13:23	May	1004	Division C		Division A	15		1011	restaurant 11	Division A	0
13	PQS1168143	20/05/2021 14:58	May	1004	Division C		Division B	10		1012	restaurant 12	Division B	0
14	PQS1170126	23/05/2021 19:43	May	1005	Division A		Division C	12		1013	restaurant 13	Division C	0
15	PQS1171572	24/05/2021 16:45	May	1002	Division B					1014	restaurant 14	Division B	0
16	PQS1171612	24/05/2021 17:13	May	1002	Division B					1015	restaurant 15	Division C	0
17	PQS1171696	24/05/2021 18:51	May	1005	Division A					1016	restaurant 16	Division C	0
18	PQS1171758	24/05/2021 20:35	May	1004	Division C					1017	restaurant 17	Division A	0
19	PQS1172770	25/05/2021 17:46	May	1001	Division A					1018	restaurant 18	Division B	0
20	PQS1172770	25/05/2021 17:46	May	1001	Division A					1019	restaurant 19	Division C	0
21	PQS1172933	26/05/2021 8:22	May	1005	Division A					1020	restaurant 20	Division C	0
22	PQS1176776	31/05/2021 21:11	May	1003	Division C					1021	restaurant 21	Division C	0
23	PQS1177527	1/06/2021 14:41	June	1002	Division B					1022	restaurant 22	Division A	0
24	PQS1178936	2/06/2021 16:49	June	1002	Division B
25	PQS1179289	3/06/2021 9:49	June	1001	Division A
26	PQS1185619	9/06/2021 12:31	June	1003	Division C
27	PQS1185829	9/06/2021 14:48	June	1001	Division A
28	PQS1185835	9/06/2021 14:51	June	1001	Division A
29	PQS1188138	11/06/2021 22:10	June	1001	Division A
30	PQS1188138	11/06/2021 22:10	June	1001	Division A
31	PQS1190034	14/06/2021 16:56	June	1002	Division B
32	PQS1194371	20/06/2021 15:18	June	1001	Division A
33	PQS1196364	22/06/2021 14:22	June	1002	Division B
34	PQS1197316	23/06/2021 14:08	June	1002	Division B
35	PQS1197597	23/06/2021 18:37	June	1003	Division C
36	PQS1205867	3/07/2021 23:01	July	1004	Division C
37	PQS1212814	13/07/2021 10:51	July	1004	Division C
38	PQS1214507	15/07/2021 10:45	July	1002	Division B
Queries

Cell Formulas
Range	Formula
H8:H10	H8	=SUMIF(L:L,G8,M:M)
H12:H14	H12	=COUNTIF(E:E,G12)
M2	M2	=COUNTIF(Queries!D:D,J2)
M3:M23	M3	=COUNTIF(D:D,J3)
E2:E38	E2	=VLOOKUP(D2,J:L,3,0)

 

[Toadstool]

Hi EliteBeat,

Yes, I also came up with a worker column solution.

EliteBeat.xlsx
	A	B	C	D	E	F	G	H
1	Number	Created	Month Created	Restaurant Number	Division		Search Division	Result
2							Division B	10
3
4
5	PQS1160998	13-05-21 17:18	May	1001	Divison A
6	PQS1160971	13-05-21 17:01	May	1001	Divison A
7
8
9	PQS1165398	18-05-21 11:06	May	1001	Divison A
Queries

Cell Formulas
Range	Formula
H2	H2	=COUNTIF($E$2:$E$99999,$G$2)
C9,C5:C6	C5	=TEXT(MONTH(B5)*29,"Mmmm")
E2:E9	E2	=IF(D2="","",INDEX(restaurant!$C$2:$C$99999,MATCH(D2,restaurant!$A$2:$A$99999,0)))

 

[EliteBeat]

Thank you very much for this, it's helped so much. What am I doing wrong with the vlookup, it's not returning anything? I should note that the whole data set contains about 9000 entries of each about 3000 don't have a restaurant number assigned to them. I'm only looking to get data on the ones which do.

excel file.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Number	Created	Month Created	Restaurant Number						restaurant No	restaurant Name	Division
2	PQS1121258	04-01-21 06:40	January		#N/A					1001	restaurant 1	Divison A	11
3	PQS1121262	04-01-21 08:20	January		#N/A					1002	restaurant 2	Division B	10
4	PQS1121293	04-01-21 09:14	January		#N/A					1003	restaurant 3	Division C	3
5	PQS1160998	13-05-21 17:18	May	1001	#N/A					1004	restaurant 4	Division C	9
6	PQS1160971	13-05-21 17:01	May	1001	#N/A					1005	restaurant 5	Divison A	19
7	PQS1121257	04-01-21 06:36	January		#N/A					1006	restaurant 6	Division B	8
8	PQS1121257	04-01-21 06:36	January		#N/A		Divison A	1530		1007	restaurant 7	Division C	5
9	PQS1165398	18-05-21 11:06	May	1001	#N/A		Division B	1966		1008	restaurant 8	Divison A	12
10	PQS1121346	04-01-21 10:28	January		#N/A		Division C	2955		1009	restaurant 9	Division B	8
11	PQS1172770	25-05-21 17:46	May	1001	#N/A					1010	restaurant 10	Division C	4
12	PQS1172770	25-05-21 17:46	May	1001	#N/A		Divison A	0		1011	restaurant 11	Divison A	4
13	PQS1179289	03-06-21 09:49	June	1001	#N/A		Division B	0		1012	restaurant 12	Division B	13
14	PQS1185835	09-06-21 14:51	June	1001	#N/A		Division C	0		1013	restaurant 13	Division C	5
15	PQS1121415	04-01-21 11:51	January		#N/A					1014	restaurant 14	Division B	7
16	PQS1121455	04-01-21 12:45	January		#N/A					1015	restaurant 15	Division C	10
17	PQS1121391	04-01-21 11:27	January		#N/A					1016	restaurant 16	Division C	17
18	PQS1121441	04-01-21 12:31	January		#N/A					1017	restaurant 17	Divison A	44
19	PQS1121385	04-01-21 11:19	January		#N/A					1018	restaurant 18	Division B	8
20	PQS1188138	11-06-21 22:10	June	1001	#N/A					1019	restaurant 19	Division C	2
Queries

Cell Formulas
Range	Formula
H8:H10	H8	=SUMIF(L:L,G8,M:M)
H12:H14	H12	=COUNTIF(E:E,G12)
C2:C20	C2	=TEXT(MONTH(B2)*29,"Mmmm")
E2:E20	E2	=VLOOKUP(D2,J:L,3,0)
M2:M20	M2	=COUNTIF(D:D,J2)

 

[EliteBeat]

Sorry, just noticed but was your spreadsheet displaying multiple ways to get the intended results? I've tidied my spreadhseet up and displayed it like this. Do I need to use the VLOOKUP?

Excel Exercise FINAL - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Number	Created	Month Created	Restaurant Number					restaurant No	restaurant Name	Division	Total Queries
2	PQS1121258	04-01-21 06:40	January						1001	restaurant 1	Divison A	11
3	PQS1121262	04-01-21 08:20	January						1002	restaurant 2	Division B	10
4	PQS1121293	04-01-21 09:14	January						1003	restaurant 3	Division C	3
5	PQS1160998	13-05-21 17:18	May	1001		Total Queries by Divison			1004	restaurant 4	Division C	9
6	PQS1160971	13-05-21 17:01	May	1001					1005	restaurant 5	Divison A	19
7	PQS1121257	04-01-21 06:36	January						1006	restaurant 6	Division B	8
8	PQS1121257	04-01-21 06:36	January			Divison A	1530		1007	restaurant 7	Division C	5
9	PQS1165398	18-05-21 11:06	May	1001		Division B	1966		1008	restaurant 8	Divison A	12
10	PQS1121346	04-01-21 10:28	January			Division C	2955		1009	restaurant 9	Division B	8
11	PQS1172770	25-05-21 17:46	May	1001					1010	restaurant 10	Division C	4
12	PQS1172770	25-05-21 17:46	May	1001					1011	restaurant 11	Divison A	4
13	PQS1179289	03-06-21 09:49	June	1001					1012	restaurant 12	Division B	13
14	PQS1185835	09-06-21 14:51	June	1001					1013	restaurant 13	Division C	5
15	PQS1121415	04-01-21 11:51	January						1014	restaurant 14	Division B	7
16	PQS1121455	04-01-21 12:45	January						1015	restaurant 15	Division C	10
17	PQS1121391	04-01-21 11:27	January						1016	restaurant 16	Division C	17
18	PQS1121441	04-01-21 12:31	January						1017	restaurant 17	Divison A	44
19	PQS1121385	04-01-21 11:19	January						1018	restaurant 18	Division B	8
20	PQS1188138	11-06-21 22:10	June	1001					1019	restaurant 19	Division C	2
21	PQS1121341	04-01-21 10:23	January						1020	restaurant 20	Division C	8
22	PQS1121313	04-01-21 09:41	January						1021	restaurant 21	Division C	7
23	PQS1188138	11-06-21 22:10	June	1001					1022	restaurant 22	Divison A	5
24	PQS1194371	20-06-21 15:18	June	1001					1023	restaurant 23	Division B	3
25	PQS1121303	04-01-21 09:26	January						1024	restaurant 24	Division C	2
26	PQS1121303	04-01-21 09:26	January						1025	restaurant 25	Division C	10
27	PQS1121321	04-01-21 09:54	January						1026	restaurant 26	Divison A	9
28	PQS1121321	04-01-21 09:54	January						1027	restaurant 27	Division B	6
29	PQS1121413	04-01-21 11:47	January						1028	restaurant 28	Divison A	4
30	PQS1121413	04-01-21 11:47	January						1029	restaurant 29	Division B	11
31	PQS1185829	09-06-21 14:48	June	1001					1030	restaurant 30	Division C	8
32	PQS1149827	27-04-21 16:05	April	1002					1031	restaurant 31	Division B	4
33	PQS1161534	14-05-21 11:50	May	1002					1032	restaurant 32	Division C	11
34	PQS1171612	24-05-21 17:13	May	1002					1033	restaurant 33	Division B	6
35	PQS1171572	24-05-21 16:45	May	1002					1034	restaurant 34	Division C	12
36	PQS1177527	01-06-21 14:41	June	1002					1035	restaurant 35	Division B	9
37	PQS1178936	02-06-21 16:49	June	1002					1036	restaurant 36	Division C	10
38	PQS1121580	04-01-21 15:48	January						1037	restaurant 37	Division C	8
39	PQS1196364	22-06-21 14:22	June	1002					1038	restaurant 38	Divison A	3
40	PQS1197316	23-06-21 14:08	June	1002					1039	restaurant 39	Division B	17
Question 4

Cell Formulas
Range	Formula
G8:G10	G8	=SUMIF(K:K,F8,L:L)
C2:C40	C2	=TEXT(MONTH(B2)*29,"Mmmm")
L2:L40	L2	=COUNTIF(D:D,I2)

 

[GraH]

Hi, the vlookup is only required if you want to retrieve the division in your queries table first. Then you can do a COUNTIF.
Or you do like you did.

 

[EliteBeat]

All sorted now, thanks everyone for your help.