Google Sheets SUMIF and SUMIFS features assist analyze, set up, and sum knowledge based mostly on particular standards.
These formulation whole cell values after they meet the necessities you set.
I lately did this for a web based enterprise that wished to dig deep into its site visitors stories.
I exported the corporate’s web site site visitors by channel from its analytics software program and imported that knowledge right into a Google Sheet, which I saved as “Each day Visitors.”
I then aggregated the day by day knowledge: one row per channel per day. The corporate usually receives day by day site visitors from roughly 9 sources. Thus from January 1 to March 28 the spreadsheet had 748 rows of channel-traffic totals. This was an excessive amount of data to kind by means of manually.
The information import resulted in 748 rows of channel-traffic totals.
Utilizing pattern knowledge, let’s take a look at how the SUMIF and SUMIFS formulation may help analyze web site site visitors, particularly:
- How a lot whole site visitors did every channel generate for the whole 87-day interval?
- How a lot did every channel generate month-to-month?
SUMIF
The imported web site site visitors data has three columns: date, channel, and the variety of distinctive visits for every channel on that day.
The supply knowledge has three columns in a sheet: Date, Channel, and Web site Visitors.
My process is to determine how a lot web site site visitors every channel generated for the whole interval: January 1 to March 28, 2022.
To start, I’ve created a brand new sheet referred to as “Whole Visitors by Channel,” with two columns: “Channel” and “Whole Visitors.”
A brand new sheet, Whole Visitors by Channel, comprises the site visitors totals for every channel for the whole interval.
I’ve used the SUMIF system to acquire the entire site visitors from every channel for the whole date vary. The system accepts two or three parameters.
The 2-parameter model consists of vary and criterion.
=SUMIF(vary, criterion)
On this case, the vary is the place the system will search for the criterion and the cells to sum.
For this instance, we want the three-parameter model, which provides a sum vary. That vary is the place Google Sheets will look when it matches the criterion. The sum vary represents the cells to be totaled.
=SUMIF(vary, criterion, sum vary)
I’ll now use the system to get the site visitors from the affiliate channel.
As I begin to kind “=SUMIF” into the system bar, Google Sheets gives the choice to pick out the SUMIF system.
Google Sheets suggests SUMIF even earlier than the typing is full.
As a result of I chosen the recommended SUMIF system, Google Sheets confirmed me a information. I can navigate across the workbook and choose columns from the Each day Visitors sheet as wanted.
Deciding on the recommended SUMIF perform simplifies the formula-creation course of.
I navigate to the “Each day Visitors” sheet and choose column B.
Subsequent, I must outline my criterion, which is the phrase “Affiliate.” As a result of I have already got this phrase in my new sheet “Whole Visitors by Channel,” I can use the cell reference, A3.
Clicking on the A3 cell provides its worth, “Affiliate,” to the SUMIF system because the criterion.
Lastly, I navigate again to the Each day Visitors sheet and choose column C, the sum vary. If Column B matches my criterion “Affiliate,” the worth in Column C of the identical row can be added to the entire.
If Column B matches my criterion, “Affiliate,” the worth in Column C of the identical row can be added to the entire.
Right here is the finished system.
=SUMIF('Each day Visitors'!B:B,A3,'Each day Visitors'!C:C)
Identical to that, we all know that the affiliate channel drove 53,875 web site visits from January 1 by means of March 28.
The SUMIF system labored. The affiliate channel drove 53,875 visits from January 1 by means of March 28.
I can seize the underside nook of this cell and drag the system right down to get the totals for every of the remaining channels.
Dragging the cell’s system down the column fills within the site visitors totals for all different channels.
The “Paid” (i.e., promoting) channel generated the lion’s share of site visitors (3,038,521 visits), which is frequent for ecommerce websites.
SUMIFS
The SUMIFS perform is much like SUMIF, besides it permits a number of standards. This characteristic helps reply the second site-traffic query, “How a lot site visitors did every channel generate month-to-month?”
The parameters for SUMIFS are in a barely totally different order.
=SUMIFS(sum vary, criterion vary, criterion)
It’s attainable to have almost limitless standards by including pairs of criterion vary and criterion.
=SUMIFS(sum vary, criterion vary 1, criterion 1, criterion vary 2, criterion 2)
I’ve created one other sheet, “Month-to-month Visitors by Channel,” with rows of channels and with columns of months.
The Month-to-month Visitors by Channel sheet is the place the SUMIFS perform will get month-to-month site visitors totals.
I’ve labored by means of the SUMIFS perform in the identical method because the SUMIF perform. As a result of it may possibly embrace many standards, the SUMIFs system can get lengthy. Ultimately, I’ve the finished system for the affiliate channel in January 2022.
=SUMIFS('Each day Visitors'!C:C,'Each day Visitors'!B:B,A2,'Each day Visitors'!A:A,">=2022-01-01",'Each day Visitors'!A:A,"<=2022-01-31")
The SUMIFs system can get lengthy owing to many standards.
Let’s break down this system.
The primary parameter is the sum vary, column C within the Each day Visitors sheet. It comprises the precise site visitors quantity.
=SUMIFS('Each day Visitors'!C:C,
The subsequent two comma-separated parameters are the primary criterion vary and the primary criterion.
'Each day Visitors'!B:B,A2
Column B within the Each day Visitors sheet (‘Each day Visitors’!B:B within the system) is the record of channels. Cell A2 holds the channel identify “Affiliate.” I might have typed “affiliate,” however utilizing the cell reference makes it attainable to pull the system down and fill the opposite channels.
The subsequent two pairs of criterion vary and criterion create a date vary. “Each day Visitors” column A holds the dates. The criterion “>=2022-01-01” specifies that the date is bigger than or equal to January 1, 2022.
'Each day Visitors'!A:A,">=2022-01-01"
I included the date as textual content “>=2022-01-01” as a result of I knew Google Sheets would acknowledge that format. One other approach of writing dates is: “>=”&date(2022,1,1).
Each will work.
I can copy and paste the system throughout the date vary for every, however in any other case the duty is full. The SUMIFs perform made the method comparatively simple. I now know the entire month-to-month site visitors generated by every channel.
The SUMIFs perform was simple to get month-to-month totals by channel.
