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

An Quala

An Quala

Board Regular
Joined
Mar 21, 2022
Messages
146
Office Version
  1. 2021
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1ProductEntityOperationCampaign IdAd Group IdPortfolio IdAd Id (Read only)Keyword Id (Read only)Product Targeting Id (Read only)Campaign NameAd Group NameCampaign Name (Informational only)Ad Group Name (Informational only)Portfolio Name (Informational only)Start DateEnd DateTargeting TypeStateCampaign State (Informational only)Ad Group State (Informational only)Daily BudgetSKUASIN (Informational only)Ad Group Default BidAd Group Default Bid (Informational only)BidKeyword TextMatch TypeBidding StrategyPlacementPercentageProduct Targeting ExpressionResolved Product Targeting Expression (Informational only)ImpressionsClicksClick-through RateSpendSalesOrdersUnitsConversion RateAcosCPCROAS
2Sponsored ProductsCampaign259139068650319Campaign ABCCampaign ABC20220101AUTOenabledenabled1.00Fixed bid000.00%0.000.00000.000.00%0.000.00
3Sponsored ProductsBidding Adjustment259139068650319Campaign ABCenabledFixed bidplacementTop0.00000.00%0.000.00000.000.00%0.000.00
4Sponsored ProductsBidding Adjustment259139068650319Campaign ABCenabledFixed bidplacementProductPage0.00000.00%0.000.00000.000.00%0.000.00
5Sponsored ProductsAd Group259139068650319257847586333260Ad GroupCampaign ABCAd Groupenabledenabledenabled0.50000.00%0.000.00000.000.00%0.000.00
6Sponsored ProductsProduct Ad259139068650319257847586333260103022664986421Campaign ABCAd Groupenabledenabledenabled000.00%0.000.00000.000.00%0.000.00
7Sponsored ProductsProduct Targeting259139068650319257847586333260258946165082246Campaign ABCAd Groupenabledenabledenabled0.500.50close-matchclose-match000.00%0.000.00000.000.00%0.000.00
8Sponsored ProductsProduct Targeting259139068650319257847586333260122119742464495Campaign ABCAd Grouppausedenabledenabled0.500.50loose-matchloose-match000.00%0.000.00000.000.00%0.000.00
9Sponsored ProductsProduct Targeting259139068650319257847586333260237992570050613Campaign ABCAd Grouppausedenabledenabled0.500.50complementscomplements000.00%0.000.00000.000.00%0.000.00
10Sponsored ProductsProduct Targeting259139068650319257847586333260232189701876219Campaign ABCAd Grouppausedenabledenabled0.500.50substitutessubstitutes000.00%0.000.00000.000.00%0.000.00
11Sponsored ProductsCampaign83506138271425142067021504153Campaign ABCCampaign ABCPortfolio DEF20211026MANUALenabledenabled1.00Dynamic bids - down only000.00%0.000.00000.000.00%0.000.00
12Sponsored ProductsBidding Adjustment83506138271425Campaign ABCPortfolio DEFenabledDynamic bids - down onlyplacementProductPage0.00000.00%0.000.00000.000.00%0.000.00
13Sponsored ProductsBidding Adjustment83506138271425Campaign ABCPortfolio DEFenabledDynamic bids - down onlyplacementTop20.00000.00%0.000.00000.000.00%0.000.00
14Sponsored ProductsAd Group8350613827142537578778386913Ad GroupCampaign ABCAd GroupPortfolio DEFenabledenabledenabled0.50000.00%0.000.00000.000.00%0.000.00
15Sponsored ProductsProduct Ad8350613827142537578778386913212342645931017Campaign ABCAd GroupPortfolio DEFenabledenabledenabledSKUABCB0XXXXXXXX000.00%0.000.00000.000.00%0.000.00
16Sponsored ProductsKeyword835061382714253757877838691323992902474108Campaign ABCAd GroupPortfolio DEFenabledenabledenabled0.500.55Keywordsphrase000.00%0.000.00000.000.00%0.000.00
17Sponsored ProductsNegative Keyword8350613827142537578778386913196272291225675Campaign ABCAd GroupPortfolio DEFenabledenabledenabledKeywordsnegativePhrase000.00%0.000.00000.000.00%0.000.00
18Sponsored ProductsNegative Keyword8350613827142537578778386913127314570814065Campaign ABCAd GroupPortfolio DEFenabledenabledenabledKeywordsnegativePhrase000.00%0.000.00000.000.00%0.000.00
19Sponsored ProductsNegative Keyword835061382714253757877838691394457677669792Campaign ABCAd GroupPortfolio DEFenabledenabledenabledKeywordsnegativePhrase000.00%0.000.00000.000.00%0.000.00
20Sponsored ProductsNegative Keyword83506138271425375787783869136340902182616Campaign ABCAd GroupPortfolio DEFenabledenabledenabledKeywordsnegativePhrase000.00%0.000.00000.000.00%0.000.00
21Sponsored ProductsNegative Keyword8350613827142537578778386913200103526221406Campaign ABCAd GroupPortfolio DEFenabledenabledenabledKeywordsnegativePhrase000.00%0.000.00000.000.00%0.000.00
22Sponsored ProductsCampaign165707835224571142067021504153Campaign ABCCampaign ABCPortfolio DEF20211214MANUALenabledenabled1.00Fixed bid000.00%0.000.00000.000.00%0.000.00
23Sponsored ProductsBidding Adjustment165707835224571Campaign ABCPortfolio DEFenabledFixed bidplacementProductPage0.00000.00%0.000.00000.000.00%0.000.00
24Sponsored ProductsBidding Adjustment165707835224571Campaign ABCPortfolio DEFenabledFixed bidplacementTop0.00000.00%0.000.00000.000.00%0.000.00
25Sponsored ProductsAd Group16570783522457175645299640614Ad GroupCampaign ABCAd GroupPortfolio DEFenabledenabledenabled0.50000.00%0.000.00000.000.00%0.000.00
26Sponsored ProductsProduct Ad16570783522457175645299640614199462666282967Campaign ABCAd GroupPortfolio DEFenabledenabledenabledSKUABCB0XXXXXXXX000.00%0.000.00000.000.00%0.000.00
27Sponsored ProductsKeyword1657078352245717564529964061433605937547982Campaign ABCAd GroupPortfolio DEFenabledenabledenabled0.500.30Keywordsbroad000.00%0.000.00000.000.00%0.000.00
28Sponsored ProductsCampaign258754573589174Campaign ABCCampaign ABC20220101AUTOenabledenabled1.00Fixed bid000.00%0.000.00000.000.00%0.000.00
29Sponsored ProductsBidding Adjustment258754573589174Campaign ABCenabledFixed bidplacementProductPage0.00000.00%0.000.00000.000.00%0.000.00
30Sponsored ProductsBidding Adjustment258754573589174Campaign ABCenabledFixed bidplacementTop0.00000.00%0.000.00000.000.00%0.000.00
31Sponsored ProductsAd Group258754573589174153129680764393Ad GroupCampaign ABCAd Groupenabledenabledenabled0.50000.00%0.000.00000.000.00%0.000.00
32Sponsored ProductsProduct Ad258754573589174153129680764393205260612829258Campaign ABCAd Groupenabledenabledenabled000.00%0.000.00000.000.00%0.000.00
33Sponsored ProductsProduct Targeting258754573589174153129680764393275458494159742Campaign ABCAd Grouppausedenabledenabled0.500.50close-matchclose-match000.00%0.000.00000.000.00%0.000.00
34Sponsored ProductsProduct Targeting258754573589174153129680764393146968282550236Campaign ABCAd Grouppausedenabledenabled0.500.50loose-matchloose-match000.00%0.000.00000.000.00%0.000.00
35Sponsored ProductsProduct Targeting258754573589174153129680764393224116759652864Campaign ABCAd Groupenabledenabledenabled0.500.50complementscomplements000.00%0.000.00000.000.00%0.000.00
36Sponsored ProductsProduct Targeting258754573589174153129680764393217549954321005Campaign ABCAd Grouppausedenabledenabled0.500.50substitutessubstitutes000.00%0.000.00000.000.00%0.000.00
37Sponsored ProductsCampaign80528116931698Campaign ABCCampaign ABC20220101MANUALenabledenabled1.00Fixed bid000.00%0.000.00000.000.00%0.000.00
38Sponsored ProductsBidding Adjustment80528116931698Campaign ABCenabledFixed bidplacementTop0.00000.00%0.000.00000.000.00%0.000.00
39Sponsored ProductsBidding Adjustment80528116931698Campaign ABCenabledFixed bidplacementProductPage0.00000.00%0.000.00000.000.00%0.000.00
40Sponsored ProductsAd Group8052811693169876107481712643Ad GroupCampaign ABCAd Groupenabledenabledenabled1.10000.00%0.000.00000.000.00%0.000.00
41Sponsored ProductsProduct Ad8052811693169876107481712643147693024949029Campaign ABCAd Groupenabledenabledenabled000.00%0.000.00000.000.00%0.000.00
42Sponsored ProductsKeyword80528116931698761074817126438859256792213Campaign ABCAd Groupenabledenabledenabled1.100.63Keywordsexact000.00%0.000.00000.000.00%0.000.00
43Sponsored ProductsKeyword8052811693169876107481712643247684218438325Campaign DEFAd Groupenabledenabledenabled1.100.63Keywordsexact000.00%0.000.00000.000.00%0.000.00
44Sponsored ProductsKeyword8052811693169876107481712643158916859273288Campaign DEFAd Groupenabledenabledenabled1.100.66Keywordsexact000.00%0.000.00000.000.00%0.000.00
45Sponsored ProductsKeyword8052811693169876107481712643118360102721703Campaign DEFAd Groupenabledenabledenabled1.100.66Keywordsexact000.00%0.000.00000.000.00%0.000.00
46Sponsored ProductsKeyword8052811693169876107481712643260134484145018Campaign DEFAd Groupenabledenabledenabled1.100.63Keywordsexact000.00%0.000.00000.000.00%0.000.00
47Sponsored ProductsKeyword805281169316987610748171264330584086520380Campaign DEFAd Groupenabledenabledenabled1.101.07Keywordsexact000.00%0.000.00000.000.00%0.000.00
48Sponsored ProductsKeyword8052811693169876107481712643263694419951020Campaign DEFAd Groupenabledenabledenabled1.100.46Keywordsexact000.00%0.000.00000.000.00%0.000.00
49Sponsored ProductsKeyword8052811693169876107481712643150620715834560Campaign DEFAd Groupenabledenabledenabled1.100.63Keywordsexact000.00%0.000.00000.000.00%0.000.00
50Sponsored ProductsKeyword8052811693169876107481712643239054561561844Campaign DEFAd Groupenabledenabledenabled1.100.79Keywordsexact000.00%0.000.00000.000.00%0.000.00
51Sponsored ProductsKeyword8052811693169876107481712643183475837427392Campaign DEFAd Groupenabledenabledenabled1.100.79Keywordsexact000.00%0.000.00000.000.00%0.000.00
BOOK1


Control Panel

Bulk File 07.06.22.xlsx
ABCDEFGH
1EntityCampaign Name (phrase match)ImpressionsClicks Spend SalesOrdersUnits
2KeywordABC
3Product TargetingDEF
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Control Panel
 

Attachments

  • Final Result.png
    Final Result.png
    36.4 KB · Views: 7
Sort by date Sort by votes
An Quala said:
can you add point 2 only please?
Click to expand...
Try this

An Quala_1.xlsm
ABJ
1EntityCampaign Name (Informational only)No of campaigns
2KeywordABC2
3KeywordDEF1
4KeywordGHI1
5Product TargetingABCD0
Control Panel
Cell Formulas
RangeFormula
J2:J5J2=IFNA(ROWS(FILTER(BOOK1!B$2:B$61,(BOOK1!B$2:B$61="Campaign")*ISNUMBER(FIND(" "&B2&" "," "&BOOK1!L$2:L$61&" "))*(BOOK1!R$2:R$61="enabled")*(BOOK1!S$2:S$61="enabled"),NA())),0)
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
You must log in or register to reply here.

Similar threads

R
  • Question
Help moving values based on cell values from one sheet to another
Replies
5
Views
144
Cubist
Cubist
I
  • Question
Move multiple rows from one table to another using conditional trigger
Replies
2
Views
486
ItsNursinTime
I
S
  • Solved
Need to have Worksheet_Change event include value from another column using structured reference
Replies
4
Views
312
Alex Blakenburg
A
An Quala
  • Locked
  • Question
Sum up the values from one sheet and add them to another following certain conditions 2
Replies
1
Views
252
Fluff
Fluff
An Quala
  • Question
Delete the Entire Row if following conditions are TRUE in Excel VBA
Replies
4
Views
488
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,429
Messages
6,124,845
Members
449,194
Latest member
HellScout

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top