Adding on to our Index Match function from the previous tutorial here we can now aggregate our data by different campaigns using Sumifs, Averageifs or countifs. This will allow us to get some summarized data for each ad campaign and for this example the total clicks for the campaigns we are running.
I know we are skipping out on if functions, but that is for another tutorial and honestly these are easier than If functions so as beginners this will help us get a basic understanding of the logic behind the if function. So lets begin with our sumifs function explaining what each part is at its basic level! =sumifs("Column or row we want to sum","Column or row we want to group by","What our criteria is to group on").
If you look at the excel table below let us go ahead and move over to the Index Match tab. We are going to add in our new campaign names from the last lesson (in cell D3 type in "=INDEX(G:G,MATCH(B3,F:F,0))", drag this down to the bottom of our data set. For more info on this click here ).
Now that we have our data set ready we can do some fun calculations! Hop on over to tab Sumifs and in cell B2 we can start to calculate our sums!
Step1: We type into Cell b2 =sumifs() and we want to figure out what we want to sum(in this instance our clicks from the index match tab), so our formula should look like =sumifs('Index Match'!C:C,)
Step2: Now that we have our data to summarize we want to add in what our Criteria range is, in this example we want to look at the actual ad sets we have, so our criteria range would be Ad Name on the index match tab. Our formula is starting to take shape and should look like this =sumifs('Index Match'!C:C,'Index Match'!B:B,)
Step3: Now comes the easy part, we have our Data we want summarized, we have what we want it summarized by now we just need to determine what our criteria is, which is just our Ad set in column A. Our formula should look like this =SUMIFS('Index Match'!C:C,'Index Match'!B:B,A2)!
So to recap what we did in the above example we have selected the data we want to sum, the data criteria range we want to group our data by and the criteria to "group" our data by.
Now we drag this formula to the end of our data table and we have succefully summed up the clicks for each Ad Set! Now for the campaign name we want to add the same formula but change what our criteria range is. Go ahead and try it yourself in cell G2(remember to change the criteria range and our criteria). The beauty of this formula is that if you want the average or the counts you can easily do that as well, just swap out the sumifs with averageifs or countifs!
Hopefully this helps you out with two of the more important functions when it comes to marketing analytics that I have found in my career. Next we will look at If functions and really get deep into our analysis.
Enjoy!