Sum up the values from one sheet and add them to another following certain conditions

[An Quala]

Hello Everyone, can you please help me with a code that will sum up values based on condition,

Here are the details,
If sheet "Control Panel" Range(A2)=sheet "BOOK1" Range(B) (search in all rows from B2 to B down)
If sheet "Control Panel" Range(B2)=sheet "BOOK1" Range(L) (search in all rows in phrase from B2 to B down) (For example if in Control Panel there is "Apple" then it should consider "Apple Juice" not "Pineapple" in campaign names)
Sum the values of AH, AI, AK, AL AM and AN from BOOK1 and write the summed values in control panel in their corresponding columns,

In control panel, we should be able to add multiple values in Columns A and B,

This is not a very complex program, I might not be able to convey it properly but if you need, I can explain it even in more details, please let me know,

Thank you!

BOOK1

Bulk File 07.06.22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	Product	Entity	Operation	Campaign Id	Ad Group Id	Portfolio Id	Ad Id (Read only)	Keyword Id (Read only)	Product Targeting Id (Read only)	Campaign Name	Ad Group Name	Campaign Name (Informational only)	Ad Group Name (Informational only)	Portfolio Name (Informational only)	Start Date	End Date	Targeting Type	State	Campaign State (Informational only)	Ad Group State (Informational only)	Daily Budget	SKU	ASIN (Informational only)	Ad Group Default Bid	Ad Group Default Bid (Informational only)	Bid	Keyword Text	Match Type	Bidding Strategy	Placement	Percentage	Product Targeting Expression	Resolved Product Targeting Expression (Informational only)	Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units	Conversion Rate	Acos	CPC	ROAS
2	Sponsored Products	Campaign		259139068650319						Campaign ABC		Campaign ABC			20220101		AUTO	enabled	enabled		1.00								Fixed bid					0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
3	Sponsored Products	Bidding Adjustment		259139068650319								Campaign ABC							enabled										Fixed bid	placementTop	0.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
4	Sponsored Products	Bidding Adjustment		259139068650319								Campaign ABC							enabled										Fixed bid	placementProductPage	0.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
5	Sponsored Products	Ad Group		259139068650319	257847586333260						Ad Group	Campaign ABC	Ad Group					enabled	enabled	enabled				0.50										0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
6	Sponsored Products	Product Ad		259139068650319	257847586333260		103022664986421					Campaign ABC	Ad Group					enabled	enabled	enabled														0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
7	Sponsored Products	Product Targeting		259139068650319	257847586333260				258946165082246			Campaign ABC	Ad Group					enabled	enabled	enabled					0.50	0.50						close-match	close-match	0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
8	Sponsored Products	Product Targeting		259139068650319	257847586333260				122119742464495			Campaign ABC	Ad Group					paused	enabled	enabled					0.50	0.50						loose-match	loose-match	0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
9	Sponsored Products	Product Targeting		259139068650319	257847586333260				237992570050613			Campaign ABC	Ad Group					paused	enabled	enabled					0.50	0.50						complements	complements	0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
10	Sponsored Products	Product Targeting		259139068650319	257847586333260				232189701876219			Campaign ABC	Ad Group					paused	enabled	enabled					0.50	0.50						substitutes	substitutes	0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
11	Sponsored Products	Campaign		83506138271425		142067021504153				Campaign ABC		Campaign ABC		Portfolio DEF	20211026		MANUAL	enabled	enabled		1.00								Dynamic bids - down only					0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
12	Sponsored Products	Bidding Adjustment		83506138271425								Campaign ABC		Portfolio DEF					enabled										Dynamic bids - down only	placementProductPage	0.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
13	Sponsored Products	Bidding Adjustment		83506138271425								Campaign ABC		Portfolio DEF					enabled										Dynamic bids - down only	placementTop	20.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
14	Sponsored Products	Ad Group		83506138271425	37578778386913						Ad Group	Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled				0.50										0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
15	Sponsored Products	Product Ad		83506138271425	37578778386913		212342645931017					Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled		SKUABC	B0XXXXXXXX											0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
16	Sponsored Products	Keyword		83506138271425	37578778386913			23992902474108				Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled					0.50	0.55	Keywords	phrase						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
17	Sponsored Products	Negative Keyword		83506138271425	37578778386913			196272291225675				Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled							Keywords	negativePhrase						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
18	Sponsored Products	Negative Keyword		83506138271425	37578778386913			127314570814065				Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled							Keywords	negativePhrase						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
19	Sponsored Products	Negative Keyword		83506138271425	37578778386913			94457677669792				Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled							Keywords	negativePhrase						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
20	Sponsored Products	Negative Keyword		83506138271425	37578778386913			6340902182616				Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled							Keywords	negativePhrase						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
21	Sponsored Products	Negative Keyword		83506138271425	37578778386913			200103526221406				Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled							Keywords	negativePhrase						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
22	Sponsored Products	Campaign		165707835224571		142067021504153				Campaign ABC		Campaign ABC		Portfolio DEF	20211214		MANUAL	enabled	enabled		1.00								Fixed bid					0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
23	Sponsored Products	Bidding Adjustment		165707835224571								Campaign ABC		Portfolio DEF					enabled										Fixed bid	placementProductPage	0.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
24	Sponsored Products	Bidding Adjustment		165707835224571								Campaign ABC		Portfolio DEF					enabled										Fixed bid	placementTop	0.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
25	Sponsored Products	Ad Group		165707835224571	75645299640614						Ad Group	Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled				0.50										0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
26	Sponsored Products	Product Ad		165707835224571	75645299640614		199462666282967					Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled		SKUABC	B0XXXXXXXX											0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
27	Sponsored Products	Keyword		165707835224571	75645299640614			33605937547982				Campaign ABC	Ad Group	Portfolio DEF				enabled	enabled	enabled					0.50	0.30	Keywords	broad						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
28	Sponsored Products	Campaign		258754573589174						Campaign ABC		Campaign ABC			20220101		AUTO	enabled	enabled		1.00								Fixed bid					0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
29	Sponsored Products	Bidding Adjustment		258754573589174								Campaign ABC							enabled										Fixed bid	placementProductPage	0.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
30	Sponsored Products	Bidding Adjustment		258754573589174								Campaign ABC							enabled										Fixed bid	placementTop	0.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
31	Sponsored Products	Ad Group		258754573589174	153129680764393						Ad Group	Campaign ABC	Ad Group					enabled	enabled	enabled				0.50										0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
32	Sponsored Products	Product Ad		258754573589174	153129680764393		205260612829258					Campaign ABC	Ad Group					enabled	enabled	enabled														0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
33	Sponsored Products	Product Targeting		258754573589174	153129680764393				275458494159742			Campaign ABC	Ad Group					paused	enabled	enabled					0.50	0.50						close-match	close-match	0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
34	Sponsored Products	Product Targeting		258754573589174	153129680764393				146968282550236			Campaign ABC	Ad Group					paused	enabled	enabled					0.50	0.50						loose-match	loose-match	0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
35	Sponsored Products	Product Targeting		258754573589174	153129680764393				224116759652864			Campaign ABC	Ad Group					enabled	enabled	enabled					0.50	0.50						complements	complements	0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
36	Sponsored Products	Product Targeting		258754573589174	153129680764393				217549954321005			Campaign ABC	Ad Group					paused	enabled	enabled					0.50	0.50						substitutes	substitutes	0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
37	Sponsored Products	Campaign		80528116931698						Campaign ABC		Campaign ABC			20220101		MANUAL	enabled	enabled		1.00								Fixed bid					0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
38	Sponsored Products	Bidding Adjustment		80528116931698								Campaign ABC							enabled										Fixed bid	placementTop	0.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
39	Sponsored Products	Bidding Adjustment		80528116931698								Campaign ABC							enabled										Fixed bid	placementProductPage	0.00			0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
40	Sponsored Products	Ad Group		80528116931698	76107481712643						Ad Group	Campaign ABC	Ad Group					enabled	enabled	enabled				1.10										0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
41	Sponsored Products	Product Ad		80528116931698	76107481712643		147693024949029					Campaign ABC	Ad Group					enabled	enabled	enabled														0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
42	Sponsored Products	Keyword		80528116931698	76107481712643			8859256792213				Campaign ABC	Ad Group					enabled	enabled	enabled					1.10	0.63	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
43	Sponsored Products	Keyword		80528116931698	76107481712643			247684218438325				Campaign DEF	Ad Group					enabled	enabled	enabled					1.10	0.63	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
44	Sponsored Products	Keyword		80528116931698	76107481712643			158916859273288				Campaign DEF	Ad Group					enabled	enabled	enabled					1.10	0.66	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
45	Sponsored Products	Keyword		80528116931698	76107481712643			118360102721703				Campaign DEF	Ad Group					enabled	enabled	enabled					1.10	0.66	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
46	Sponsored Products	Keyword		80528116931698	76107481712643			260134484145018				Campaign DEF	Ad Group					enabled	enabled	enabled					1.10	0.63	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
47	Sponsored Products	Keyword		80528116931698	76107481712643			30584086520380				Campaign DEF	Ad Group					enabled	enabled	enabled					1.10	1.07	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
48	Sponsored Products	Keyword		80528116931698	76107481712643			263694419951020				Campaign DEF	Ad Group					enabled	enabled	enabled					1.10	0.46	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
49	Sponsored Products	Keyword		80528116931698	76107481712643			150620715834560				Campaign DEF	Ad Group					enabled	enabled	enabled					1.10	0.63	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
50	Sponsored Products	Keyword		80528116931698	76107481712643			239054561561844				Campaign DEF	Ad Group					enabled	enabled	enabled					1.10	0.79	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
51	Sponsored Products	Keyword		80528116931698	76107481712643			183475837427392				Campaign DEF	Ad Group					enabled	enabled	enabled					1.10	0.79	Keywords	exact						0	0	0.00%	0.00	0.00	0	0	0.00	0.00%	0.00	0.00
BOOK1

Control Panel

Bulk File 07.06.22.xlsx
	A	B	C	D	E	F	G	H
1	Entity	Campaign Name (phrase match)	Impressions	Clicks	Spend	Sales	Orders	Units
2	Keyword	ABC
3	Product Targeting	DEF
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Control Panel

 

[An Quala]

@Peter_SSs Please check.

 

[Peter_SSs]

With XL2BB, hide irrelevant columns to keep the mini-sheet smaller.
I have added some values in 'BOOK1'
Also, check that your headings match - there is a trailing space on a couple of them in your sample data. That would make the formula fail.

An Quala.xlsm
	B										L																						AH	AI	AJ	AK	AL	AM	AN
1	Entity										Campaign Name																						Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units
2	Campaign										Campaign ABC																						4	7	1	4	3	5	8
3	Bidding Adjustment										Campaign ABC																						4	4	9	4	4	4	7
4	Bidding Adjustment										Campaign ABC																						4	6	9	7	7	1	2
5	Ad Group										Campaign ABC																						7	3	8	9	8	7	9
6	Product Ad										Campaign ABC																						4	2	8	5	1	6	3
7	Product Targeting										Campaign ABC																						6	1	6	6	3	5	2
8	Product Targeting										Campaign ABC																						8	3	1	3	4	4	7
9	Product Targeting										Campaign ABC																						1	7	6	2	9	5	3
10	Product Targeting										Campaign ABC																						1	5	1	9	5	8	1
11	Campaign										Campaign ABC																						4	2	5	9	7	5	8
12	Bidding Adjustment										Campaign ABC																						4	6	8	3	5	9	1
13	Bidding Adjustment										Campaign ABC																						5	9	8	9	1	1	3
14	Ad Group										Campaign ABC																						3	9	3	6	1	8	2
15	Product Ad										Campaign ABC																						7	3	3	3	8	4	1
16	Keyword										Campaign ABC																						3	7	3	7	2	1	8
17	Negative Keyword										Campaign ABC																						6	5	8	1	9	2	6
18	Negative Keyword										Campaign ABC																						8	5	7	6	6	1	4
19	Negative Keyword										Campaign ABC																						3	4	6	9	2	1	2
20	Negative Keyword										Campaign ABC																						8	8	2	6	4	4	7
21	Negative Keyword										Campaign ABC																						5	5	4	4	5	2	5
22	Campaign										Campaign ABC																						7	3	1	1	7	9	2
23	Bidding Adjustment										Campaign ABC																						6	8	2	1	5	3	3
24	Bidding Adjustment										Campaign ABC																						3	3	3	1	4	1	4
25	Ad Group										Campaign ABC																						2	3	9	2	7	5	9
26	Product Ad										Campaign ABC																						3	2	8	9	8	9	6
27	Keyword										Campaign ABC																						8	4	1	7	2	5	4
28	Campaign										Campaign ABC																						1	8	9	5	7	7	3
29	Bidding Adjustment										Campaign ABC																						3	1	8	5	6	6	6
30	Bidding Adjustment										Campaign ABC																						2	3	5	7	3	9	2
31	Ad Group										Campaign ABC																						9	7	6	9	1	1	3
32	Product Ad										Campaign ABC																						8	4	6	5	4	8	5
33	Product Targeting										Campaign ABC																						9	9	1	7	9	8	3
34	Product Targeting										Campaign ABC																						9	5	8	9	4	6	3
35	Product Targeting										Campaign ABC																						3	7	6	7	9	9	1
36	Product Targeting										Campaign ABC																						7	8	8	5	7	9	9
37	Campaign										Campaign ABC																						1	1	7	9	3	3	2
38	Bidding Adjustment										Campaign ABC																						6	6	9	2	7	9	3
39	Bidding Adjustment										Campaign ABC																						6	5	8	8	5	2	8
40	Ad Group										Campaign ABC																						4	3	4	9	4	3	1
41	Product Ad										Campaign ABC																						6	1	9	1	6	4	3
42	Keyword										Campaign ABC																						4	5	7	8	4	2	5
43	Keyword										Campaign DEF																						1	1	7	9	5	5	3
44	Keyword										Campaign DEF																						7	7	9	7	5	8	6
45	Keyword										Campaign DEF																						4	7	5	6	8	3	6
46	Keyword										Campaign DEF																						6	1	4	6	7	3	5
47	Keyword										Campaign DEF																						1	8	3	7	2	1	6
48	Keyword										Campaign DEF																						1	7	7	3	9	6	4
49	Keyword										Campaign DEF																						8	3	7	7	4	1	6
50	Keyword										Campaign DEF																						9	1	6	3	4	6	6
51	Keyword										Campaign DEF																						4	6	5	1	3	2	9
BOOK1

An Quala.xlsm
	A	B	C	D	E	F	G	H
1	Entity	Campaign Name	Impressions	Clicks	Spend	Sales	Orders	Units
2	Keyword	ABC	15	16	22	8	8	17
3	Product Targeting	DEF	0	0	0	0	0	0
Control Panel

Cell Formulas
Range	Formula
C2:H3	C2	=SUM(FILTER(INDEX(BOOK1!$AH$2:$AN$51,0,MATCH(C$1,BOOK1!$AH$1:$AN$1,0)),((BOOK1!$B$2:$B$51=$A2)*ISNUMBER(SEARCH(" "&$B2&" "," "&BOOK1!$L$2:$L$51&" "))),0))

 

[An Quala]

Hello @Peter_SSs Sorry I had to add a few more details in the last query because last time it was difficult to focus on tiny details, I also made a separate post about this but admins asked me to continue this thread, so it would be great if you add up the following details into the last requirements, thanks!

So I need to add these details,

1- Before summing up it should also check in Sheet "BOOK1" Column R,S,T to be "enabled", if not, ignore the values.

2- Add one more column (No of campaigns) in Control Panel which Sum up the no of correspondences of that keyword in Column "Campaign Name (Information Only" (L) for example "ABC" occurs just 1 time in the sample data (screenshot attached) and columns R and S = "enabled" and Column B = "Campaign", so for EXAMPLE: for row 3, it will look something like this, B3 = Campaign, R3 = enabled, S3 = enabled, L3 contained "ABC", then 1 occurrence and so on.

3- The number of rows in the data is not fixed, in this case it is 61, but it should be unlimited.

Thank you.

BOOK1

Book3.xlsx
	B										L						R	S	T														AH	AI	AJ	AK	AL	AM	AN
1	Entity										Campaign Name (Informational only)						State	Campaign State (Informational only)	Ad Group State (Informational only)														Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units
2	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	10502.00%	10.00	100.00	5	6
3	Campaign										Campaign ABC						enabled	enabled															100	10	10602.00%	10.00	100.00	5	6
4	Bidding Adjustment										Campaign ABC							enabled															100	10	10702.00%	10.00	100.00	5	6
5	Bidding Adjustment										Campaign ABC							enabled															100	10	10802.00%	10.00	100.00	5	6
6	Ad Group										Campaign ABC						enabled	enabled	enabled														100	10	10902.00%	10.00	100.00	5	6
7	Product Ad										Campaign ABC						enabled	enabled	enabled														100	10	11002.00%	10.00	100.00	5	6
8	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	11102.00%	10.00	100.00	5	6
9	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	11202.00%	10.00	100.00	5	6
10	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	11302.00%	10.00	100.00	5	6
11	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	11402.00%	10.00	100.00	5	6
12	Product Targeting										Campaign ABC						enabled	enabled	enabled														100	10	11502.00%	10.00	100.00	5	6
13	Campaign										Campaign ABC						paused	paused															100	10	11602.00%	10.00	100.00	5	6
14	Bidding Adjustment										Campaign ABC							paused															100	10	11702.00%	10.00	100.00	5	6
15	Bidding Adjustment										Campaign ABC							paused															100	10	11802.00%	10.00	100.00	5	6
16	Ad Group										Campaign ABC						enabled	paused	enabled														100	10	11902.00%	10.00	100.00	5	6
17	Product Ad										Campaign ABC						enabled	paused	enabled														100	10	12002.00%	10.00	100.00	5	6
18	Keyword										Campaign ABC						enabled	paused	enabled														100	10	12102.00%	10.00	100.00	5	6
19	Keyword										Campaign ABC						paused	paused	enabled														100	10	12202.00%	10.00	100.00	5	6
20	Keyword										Campaign ABC						paused	paused	enabled														100	10	12302.00%	10.00	100.00	5	6
21	Keyword										Campaign ABC						paused	paused	enabled														100	10	12402.00%	10.00	100.00	5	6
22	Keyword										Campaign DEF						paused	paused	enabled														100	10	12502.00%	10.00	100.00	5	6
23	Keyword										Campaign DEF						enabled	paused	enabled														100	10	12602.00%	10.00	100.00	5	6
24	Keyword										Campaign DEF						enabled	paused	enabled														100	10	12702.00%	10.00	100.00	5	6
25	Keyword										Campaign DEF						enabled	paused	enabled														100	10	12802.00%	10.00	100.00	5	6
26	Keyword										Campaign DEF						enabled	paused	enabled														100	10	12902.00%	10.00	100.00	5	6
27	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13002.00%	10.00	100.00	5	6
28	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13102.00%	10.00	100.00	5	6
29	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13202.00%	10.00	100.00	5	6
30	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13302.00%	10.00	100.00	5	6
31	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13402.00%	10.00	100.00	5	6
32	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13502.00%	10.00	100.00	5	6
33	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13602.00%	10.00	100.00	5	6
34	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13702.00%	10.00	100.00	5	6
35	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13802.00%	10.00	100.00	5	6
36	Keyword										Campaign DEF						enabled	paused	enabled														100	10	13902.00%	10.00	100.00	5	6
37	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14002.00%	10.00	100.00	5	6
38	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14102.00%	10.00	100.00	5	6
39	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14202.00%	10.00	100.00	5	6
40	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14302.00%	10.00	100.00	5	6
41	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14402.00%	10.00	100.00	5	6
42	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14502.00%	10.00	100.00	5	6
43	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14602.00%	10.00	100.00	5	6
44	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14702.00%	10.00	100.00	5	6
45	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14802.00%	10.00	100.00	5	6
46	Keyword										Campaign DEF						enabled	paused	enabled														100	10	14902.00%	10.00	100.00	5	6
47	Keyword										Campaign DEF						enabled	paused	enabled														100	10	15002.00%	10.00	100.00	5	6
48	Keyword										Campaign DEF						enabled	paused	enabled														100	10	15102.00%	10.00	100.00	5	6
49	Keyword										Campaign DEF						enabled	paused	enabled														100	10	15202.00%	10.00	100.00	5	6
50	Keyword										Campaign DEF						enabled	paused	enabled														100	10	15302.00%	10.00	100.00	5	6
51	Keyword										Campaign DEF						enabled	paused	enabled														100	10	15402.00%	10.00	100.00	5	6
52	Keyword										Campaign GHI						enabled	paused	enabled														100	10	15502.00%	10.00	100.00	5	6
53	Keyword										Campaign GHI						enabled	paused	enabled														100	10	15602.00%	10.00	100.00	5	6
54	Keyword										Campaign GHI						enabled	paused	enabled														100	10	15702.00%	10.00	100.00	5	6
55	Keyword										Campaign GHI						enabled	paused	enabled														100	10	15802.00%	10.00	100.00	5	6
56	Keyword										Campaign GHI						enabled	paused	enabled														100	10	15902.00%	10.00	100.00	5	6
57	Keyword										Campaign GHI						enabled	paused	enabled														100	10	16002.00%	10.00	100.00	5	6
58	Keyword										Campaign GHI						enabled	paused	enabled														100	10	16102.00%	10.00	100.00	5	6
59	Keyword										Campaign GHI						enabled	paused	enabled														100	10	16202.00%	10.00	100.00	5	6
60	Keyword										Campaign GHI						enabled	paused	enabled														100	10	16302.00%	10.00	100.00	5	6
61	Keyword										Campaign GHI						enabled	paused	enabled														100	10	16402.00%	10.00	100.00	5	6
BOOK1

Control Panel

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Entity	Campaign Name (Informational only)	Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units	Number of Campaigns
2	Keyword	ABC	900	90	1045.18	90	900	45	54
3	Keyword	DEF	3000	300	4185.6	300	3000	150	180
4	Keyword	GHI	1000	100	1595.2	100	1000	50	60
5	Product Targeting	ABC	100	10	115.02	10	100	5	6
Control Panel

Cell Formulas
Range	Formula
C2:I5	C2	=SUM(FILTER(INDEX(BOOK1!$AH$2:$AN$61,0,MATCH(C$1,BOOK1!$AH$1:$AN$1,0)),((BOOK1!$B$2:$B$61=$A2)*ISNUMBER(SEARCH(" "&$B2&" "," "&BOOK1!$L$2:$L$61&" "))),0))

 

[Peter_SSs]

Before summing up it should also check in Sheet "BOOK1" Column R,S,T to be "enabled",

Do all 3 need to be "enabled", or just any one of them, or something else?

 

[An Quala]

Hello for point no 1, all 3 Columns R,S and T should be "enabled", if either one of them is not, then ignore the value.

For point no 2, only 2 Columns, R and S should be "enabled" just like first one.

Thank you.

 

[Peter_SSs]

1- Before summing up it should also check in Sheet "BOOK1" Column R,S,T to be "enabled", if not, ignore the values.

Is this what you mean?

An Quala_1.xlsm
	A	B	C	D	E	F	G	H	I
1	Entity	Campaign Name (Informational only)	Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units
2	Keyword	ABC	500	50	555.1	50	500	25	30
3	Keyword	DEF	0	0	0	0	0	0	0
4	Keyword	GHI	0	0	0	0	0	0	0
5	Product Targeting	ABC	100	10	115.02	10	100	5	6
Control Panel

Cell Formulas
Range	Formula
C2:I5	C2	=SUM(FILTER(INDEX(BOOK1!$AH$2:$AN$61,0,MATCH(C$1,BOOK1!$AH$1:$AN$1,0)),((BOOK1!$B$2:$B$61=$A2)*ISNUMBER(SEARCH(" "&$B2&" "," "&BOOK1!$L$2:$L$61&" "))*(BOOK1!$R$2:$R$61&BOOK1!$S$2:$S$61&BOOK1!$T$2:$T$61="EnabledEnabledEnabled")),0))

2- Add one more column (No of campaigns) in Control Panel which Sum up the no of correspondences of that keyword in Column "Campaign Name (Information Only" (L) for example "ABC" occurs just 1 time in the sample data (screenshot attached) and columns R and S = "enabled" and Column B = "Campaign", so for EXAMPLE: for row 3, it will look something like this, B3 = Campaign, R3 = enabled, S3 = enabled, L3 contained "ABC", then 1 occurrence and so on.

I'm still not entirely sure what is required here. For the 'Control Panel' shown in post #4, what would be the actual expected results in each of J2:J5 for the sample data provided in that post?

3- The number of rows in the data is not fixed, in this case it is 61, but it should be unlimited.

Just choose a number big enough to cover any expected data size, but I would highly recommend not using whole column references as they may bog your sheet's performance right down.
So if your data might be, say a maximum of a few hundred rows, then change the 61 to, say, 1000.

 

[An Quala]

Is this what you mean?

1- Yeah it's correct, we also have another option, as entity would always be either "Keyword" or "Product Targeting", can we embed these values in the formula instead of typing them into the cells? As campaign name would be unique for both so they won't get mixed.

I'm still not entirely sure what is required here. For the 'Control Panel' shown in post #4, what would be the actual expected results in each of J2:J5 for the sample data provided in that post?

2- Simply put, how many times does the campaign name is occurring containing this keyword with enabled status in R and S, but in "Campaign" Entity not keyword or product targeting entity, I have attached the sample data with resulted values (changed data a little bit than last time to make it better). You can also refer to the attached picture to understand how to apply filters in the data, it would be understandable.
Here is the formula for campaign occurence of "ABC",
=IF(AND($B2="Campaign",ISNUMBER(SEARCH("ABC",$L2)),$R2="enabled",$S2="enabled"),1,0) will give you 2 occurences in the data.

Just choose a number big enough to cover any expected data size, but I would highly recommend not using whole column references as they may bog your sheet's performance right down.

3- Sure.

Book3.xlsx
	B										L						R	S	T														AH	AI	AJ	AK	AL	AM	AN
1	Entity										Campaign Name (Informational only)						State	Campaign State (Informational only)	Ad Group State (Informational only)														Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units
2	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	10502.00%	10.00	100.00	5	6
3	Campaign										Campaign ABC						enabled	enabled															100	10	10602.00%	10.00	100.00	5	6
4	Bidding Adjustment										Campaign ABC						enabled	enabled															100	10	10702.00%	10.00	100.00	5	6
5	Bidding Adjustment										Campaign ABC						enabled	enabled															100	10	10802.00%	10.00	100.00	5	6
6	Ad Group										Campaign ABC						enabled	enabled	enabled														100	10	10902.00%	10.00	100.00	5	6
7	Product Ad										Campaign ABC						enabled	enabled	enabled														100	10	11002.00%	10.00	100.00	5	6
8	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	11102.00%	10.00	100.00	5	6
9	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	11202.00%	10.00	100.00	5	6
10	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	11302.00%	10.00	100.00	5	6
11	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	11402.00%	10.00	100.00	5	6
12	Product Targeting										Campaign ABC						enabled	enabled	enabled														100	10	11502.00%	10.00	100.00	5	6
13	Campaign										Campaign ABC						enabled	enabled															100	10	11602.00%	10.00	100.00	5	6
14	Bidding Adjustment										Campaign ABC						enabled	enabled															100	10	11702.00%	10.00	100.00	5	6
15	Bidding Adjustment										Campaign ABC						enabled	enabled															100	10	11802.00%	10.00	100.00	5	6
16	Ad Group										Campaign ABC						enabled	enabled	enabled														100	10	11902.00%	10.00	100.00	5	6
17	Product Ad										Campaign ABC						enabled	enabled	enabled														100	10	12002.00%	10.00	100.00	5	6
18	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	12102.00%	10.00	100.00	5	6
19	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	12202.00%	10.00	100.00	5	6
20	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	12302.00%	10.00	100.00	5	6
21	Keyword										Campaign ABC						enabled	enabled	enabled														100	10	12402.00%	10.00	100.00	5	6
22	Campaign										Campaign DEF						enabled	enabled															100	10	12502.00%	10.00	100.00	5	6
23	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	12602.00%	10.00	100.00	5	6
24	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	12702.00%	10.00	100.00	5	6
25	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	12802.00%	10.00	100.00	5	6
26	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	12902.00%	10.00	100.00	5	6
27	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	13002.00%	10.00	100.00	5	6
28	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	13102.00%	10.00	100.00	5	6
29	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	13202.00%	10.00	100.00	5	6
30	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	13302.00%	10.00	100.00	5	6
31	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	13402.00%	10.00	100.00	5	6
32	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	13502.00%	10.00	100.00	5	6
33	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	13602.00%	10.00	100.00	5	6
34	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	13702.00%	10.00	100.00	5	6
35	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	13802.00%	10.00	100.00	5	6
36	Campaign										Campaign DEF						enabled	paused															100	10	13902.00%	10.00	100.00	5	6
37	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14002.00%	10.00	100.00	5	6
38	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14102.00%	10.00	100.00	5	6
39	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14202.00%	10.00	100.00	5	6
40	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14302.00%	10.00	100.00	5	6
41	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14402.00%	10.00	100.00	5	6
42	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14502.00%	10.00	100.00	5	6
43	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14602.00%	10.00	100.00	5	6
44	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14702.00%	10.00	100.00	5	6
45	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14802.00%	10.00	100.00	5	6
46	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	14902.00%	10.00	100.00	5	6
47	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	15002.00%	10.00	100.00	5	6
48	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	15102.00%	10.00	100.00	5	6
49	Campaign										Campaign DEF						paused	enabled															100	10	15202.00%	10.00	100.00	5	6
50	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	15302.00%	10.00	100.00	5	6
51	Keyword										Campaign DEF						enabled	enabled	enabled														100	10	15402.00%	10.00	100.00	5	6
52	Keyword										Campaign GHI						enabled	enabled	enabled														100	10	15502.00%	10.00	100.00	5	6
53	Keyword										Campaign GHI						enabled	enabled	enabled														100	10	15602.00%	10.00	100.00	5	6
54	Keyword										Campaign GHI						enabled	enabled	enabled														100	10	15702.00%	10.00	100.00	5	6
55	Keyword										Campaign GHI						enabled	enabled	enabled														100	10	15802.00%	10.00	100.00	5	6
56	Keyword										Campaign GHI						enabled	enabled	enabled														100	10	15902.00%	10.00	100.00	5	6
57	Keyword										Campaign GHI						enabled	enabled	enabled														100	10	16002.00%	10.00	100.00	5	6
58	Campaign										Campaign GHI						enabled	enabled															100	10	16102.00%	10.00	100.00	5	6
59	Keyword										Campaign GHI						enabled	enabled	enabled														100	10	16202.00%	10.00	100.00	5	6
60	Keyword										Campaign GHI						enabled	enabled	enabled														100	10	16302.00%	10.00	100.00	5	6
61	Keyword										Campaign GHI						enabled	enabled	enabled														100	10	16402.00%	10.00	100.00	5	6
BOOK1

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Entity	Campaign Name (Informational only)	Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units	No of campaigns
2	Keyword	ABC	900	90	1045.18	90	900	45	54	2
3	Keyword	DEF	2700	270	3769.54	270	2700	135	162	1
4	Keyword	GHI	900	90	1434.18	90	900	45	54	1
5	Product Targeting	ABCD	0	0	0	0	0	0	0	0
Control Panel

Cell Formulas
Range	Formula
C2:I5	C2	=SUM(FILTER(INDEX(BOOK1!$AH$2:$AN$61,0,MATCH(C$1,BOOK1!$AH$1:$AN$1,0)),((BOOK1!$B$2:$B$61=$A2)*ISNUMBER(SEARCH(" "&$B2&" "," "&BOOK1!$L$2:$L$61&" "))*(BOOK1!$R$2:$R$61&BOOK1!$S$2:$S$61&BOOK1!$T$2:$T$61="EnabledEnabledEnabled")),0))

 

[Peter_SSs]

Here are the details,

So I need to add these details,

we also have another option, ....., can we embed these values in the formula ..

Hard to keep up with the changes. Please review the Guidelines, particularly 5a.

 

[An Quala]

Sure, please forget about the point 1, can you add point 2 only please?