You can run a COUNTIFS function in Excel that matches multiple criteria points to count a particular range in your data. This example is going to show how I can count all the mobile keyword’s that are in the top 3 positions in Google for my website. I can also use this formula with a macro so I can compare time periods against each other to see if I am increasing the amount of keywords in the top 3 spots for mobile. As a note, I am using data from Google Search Console for this example.
How to Run a COUNTIFS Function in Excel
With the raw data from Google Analytics, AdWords, Bing Ads, or any other data platform, I am going to create a new tab to run this formula.
This is my Raw Data in Excel
This is my calculations sheet where I have my formula . As a note, you can call this sheet whatever you like.
COUNTIFS Function Explained
The goal of this function in this example is to see how many keywords were in the top 3 place in Google during this time period for mobile searches.
The start of the formula in this example begins in cell H4 on my calculations tab. Sheet 1 actually represents the data from my raw data (see the above sheet for the raw data). You start the formula by telling Excel that you want to count all the cells that have some sort of device data in it. The next part of the formula is our first criteria that we are creating to make this function work.
The first criteria after selecting all the data in column F on the raw data sheet (sheet1) is to tell Excel to only count cells that have the word mobile on it. As a note, you have to put mobile in quotes to make this work.
The second criteria is to look at my raw data and focus on the column that says “TRUNC”. From here, I tell excel to match the criteria for mobile, but just count cells that are less than 4 in their average place. This is a multiple match in excel. I am telling excel to first count mobile cells and then to only include mobile cells that are less than 4 in their average place. As you can see, I have a less than four formula at the end of this function. Just like mobile, you need to have this in quotes to make this work.
You can then repeat this step but change out the name of the device for desktop and tablet keywords.
You can have more than two criteria for this function in excel. The COUNTIFS formula allows you to have multiple criteria points set up to return a specific value that you are looking for. You can also change the arrange of the formula if you would like. For that example above, I could have started with the TRUNC column and then matched it to the device.