The next two pairs of criterion range and criterion create a date range. Daily Traffic column A holds the dates. The criterion “>=2022-01-01” specifies that the date is greater than or equal to January 1, 2022.
Using sample data, let’s look at how the SUMIF and SUMIFS formulas can help analyze site traffic, specifically:
The two-parameter version includes “range” and “criterion.”
I used the SUMIF formula to obtain the total traffic from each channel for the entire date range. The formula accepts two or three parameters.
I can grab the bottom corner of this cell and drag the formula down to get the totals for each of the remaining channels.
For this example, we need the three-parameter version, which adds a “sum range.” That range is where Google Sheets will look when it matches the criterion. The sum range represents the cells to be totaled.
I navigate to the “Daily Traffic” sheet and select column B.
- How much traffic did each channel generate for the entire 87-day period?
- How much did each channel generate monthly?
SUMIF
These formulas total cell values when they meet the requirements you set.
Google Sheets suggests SUMIF even before the typing is complete.
Clicking on the A3 cell adds its value, “Affiliate,” to the SUMIF formula as the criterion.
Next, I need to define my criterion, which is the word “Affiliate.” Because I already have this word in my new sheet, Total Traffic by Channel, I can use the cell reference, A3.
I’ve worked through the SUMIFS function in the same manner as the SUMIF function. Because it can include many criteria, the SUMIFs formula can get long. Eventually, I have the completed formula for the affiliate channel in January 2022.
Both will work.
I exported the company’s daily site traffic by channel from its analytics software and imported that data into a Google Sheet, which I saved as “Daily Traffic.”
Selecting the suggested SUMIF function simplifies the formula-creation process.
The source data has three columns in a sheet: Date, Channel, and Site Traffic.
‘Daily Traffic’!B:B,A2
The Monthly Traffic by Channel sheet is where the SUMIFS function gets monthly traffic totals.
Here is the completed formula.
=SUMIF(‘Daily Traffic’!B:B,A3,’Daily Traffic’!C:C)
Because I selected the suggested SUMIF formula, Google Sheets showed me a guide. I can navigate around the workbook and select columns from the Daily Traffic sheet as needed.
As I start to type “=SUMIF” into the formula bar, Google Sheets provides the option to select the SUMIF formula.
I then aggregated the data: one row per channel per day. The company receives traffic from roughly nine sources. Thus from January 1 to March 28 (87 days), the spreadsheet had 748 rows of channel-traffic totals. This was too much information to sort through manually.
If Column B matches my criterion, “Affiliate,” the value in Column C of the same row will be added to the total.
The first parameter is the sum range, column C in the Daily Traffic sheet. It contains the actual traffic volume.
Let’s break down this formula.
Dragging the cell’s formula down the column fills in the traffic totals for all other channels.
The parameters for SUMIFS are in a slightly different order.
The SUMIFS function is similar to SUMIF, except it allows multiple criteria. This feature helps answer the second question, “How much traffic did each channel generate monthly?”
The SUMIF formula worked. The affiliate channel drove 53,875 visits from January 1 through March 28.
To begin, I created a sheet called “Total Traffic by Channel,” with two columns: “Channel” and “Total Traffic.”
=SUMIFS(‘Daily Traffic’!C:C,
Finally, I navigate back to the Daily Traffic sheet and select column C, the sum range. If Column B matches my criterion “Affiliate,” the value in Column C of the same row will be added to the total.
The imported traffic info has three columns: date, channel, and the number of unique visits for each channel on that day.
=SUMIFS(sum range, criterion range, criterion)
The “Paid” (i.e., advertising) channel generated the lion’s share of traffic (3,038,521 visits), which is common for ecommerce sites.
I recently did this for an online business that wanted to dig deep into its traffic reports.
=SUMIF(range, criterion)
The SUMIFs function was straightforward to get monthly totals by channel.
‘Daily Traffic’!A:A,”>=2022-01-01″
=SUMIFS(sum range, criterion range 1, criterion 1, criterion range 2, criterion 2)
Google Sheets SUMIF and SUMIFS functions help analyze, organize, and sum data based on specific criteria.
The data import resulted in 748 rows of channel-traffic totals.
I can copy and paste the formula across the date range for each, but otherwise the task is complete. The SUMIFs function made the process relatively straightforward. I now know the total monthly traffic generated by each channel.
My first task is to figure out how much traffic each channel generated for the entire 87 days.
=SUMIFS(‘Daily Traffic’!C:C,’Daily Traffic’!B:B,A2,’Daily Traffic’!A:A,”>=2022-01-01″,’Daily Traffic’!A:A,”<=2022-01-31″)
=SUMIF(range, criterion, sum range)
A new sheet, Total Traffic by Channel, contains the traffic totals for each channel for the entire period.
