What is the SUMIF Function?
The SUMIF Function tells excel to sum a particular range that meets the criteria of the value of a particular cell.
Why Use This Function?
For starters, we can use this function when we create macros in excel. This formula can be used for a range of data entry labels. For this blog, I am going to show how to view the clicks received from a desktop, mobile, and tablet device.
Raw Data
This data was all pulled from Google Analytics for my blog. I want to see the breakdown by device type for my blog for the entire year. As a note, I mentioned that you can use this as a macro. If I wanted, I could run this report every month and have the data go right into the SUMIF function to get a quick breakdown of my device story. You can then add charts to this function, but we are going to focus on the data for this blog.
Function Tutorial
In another tab, I have these two columns set up. As a note, you need to have the names match up to what the raw data says. So such as, I can’t put smart phone if the raw data says mobile. The formula will not work unless both are the same name. Here is the formula that I used below.
SUMIF Function Explained
The way this formula works is like this. In cell B2, I started the formula with the equals sign. The “TM Blast By the Numbers” is actually the name of the tab with all the raw data from Google Analytics. Column D represents the devices from the raw data. You put in “desktop” since you want B2 to reference the data for desktop visits. With the formula still running, you then go into the column with all the sessions that happened. Then you close the formula and hit enter.
If you notice an error with this formula, you can try this trouble shoot steps. Make sure you are referring to the right tab. Always make sure you are properly naming your raw data so you know where you are looking. Second, you want to make sure the names of the devices are the same. Even if they are, you might have a space at the end of the name that will throw off the formula. To solve that, you will want to TRIM the data.