Google Sheets SUMIF and SUMIFS capabilities assist analyze, manage, and sum knowledge primarily based on particular standards.
These formulation complete cell values once they meet the necessities you set.
I just lately did this for a web-based enterprise that wished to dig deep into its site visitors experiences.
I exported the corporate’s day by day website site visitors by channel from its analytics software program and imported that knowledge right into a Google Sheet, which I saved as “Every day Site visitors.”
I then aggregated the information: one row per channel per day. The corporate receives site visitors from roughly 9 sources. Thus from January 1 to March 28 (87 days), the spreadsheet had 748 rows of channel-traffic totals. This was an excessive amount of info to kind via manually.
Utilizing pattern knowledge, let’s have a look at how the SUMIF and SUMIFS formulation may help analyze website site visitors, particularly:
- How a lot site visitors did every channel generate for your complete 87-day interval?
- How a lot did every channel generate month-to-month?
The imported site visitors data has three columns: date, channel, and the variety of distinctive visits for every channel on that day.
My first activity is to determine how a lot site visitors every channel generated for your complete 87 days.
To start, I created a sheet known as “Whole Site visitors by Channel,” with two columns: “Channel” and “Whole Site visitors.”
I used the SUMIF system to acquire the whole site visitors from every channel for your complete date vary. The system accepts two or three parameters.
The 2-parameter model consists of “vary” and “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’d like 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 supplies the choice to pick the SUMIF system.
As a result of I chosen the steered SUMIF system, Google Sheets confirmed me a information. I can navigate across the workbook and choose columns from the Every day Site visitors sheet as wanted.
I navigate to the “Every day Site 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 Site visitors by Channel, I can use the cell reference, A3.
Lastly, I navigate again to the Every day Site 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 will likely be added to the whole.
Right here is the finished system.
=SUMIF('Every day Site visitors'!B:B,A3,'Every day Site visitors'!C:C)
Similar to that, we all know that the affiliate channel drove 53,875 website visits from January 1 via 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.
The “Paid” (i.e., promoting) channel generated the lion’s share of site visitors (3,038,521 visits), which is widespread for ecommerce websites.
The SUMIFS perform is much like SUMIF, besides it permits a number of standards. This function helps reply the second query, “How a lot site visitors did every channel generate month-to-month?”
The parameters for SUMIFS are in a barely completely 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 Site visitors by Channel,” with rows of channels and with columns of months.
I’ve labored via the SUMIFS perform in the identical method because the SUMIF perform. As a result of it may well embody many standards, the SUMIFs system can get lengthy. Finally, I’ve the finished system for the affiliate channel in January 2022.
=SUMIFS('Every day Site visitors'!C:C,'Every day Site visitors'!B:B,A2,'Every day Site visitors'!A:A,">=2022-01-01",'Every day Site visitors'!A:A,"<=2022-01-31")
Let’s break down this system.
The primary parameter is the sum vary, column C within the Every day Site visitors sheet. It incorporates the precise site visitors quantity.
=SUMIFS('Every day Site visitors'!C:C,
The following two comma-separated parameters are the primary criterion vary and the primary criterion.
'Every day Site visitors'!B:B,A2
Column B within the Every day Site visitors sheet (‘Every day Site visitors’!B:B within the system) is the checklist of channels. Cell A2 holds the channel identify “Affiliate.” I may have typed “affiliate,” however utilizing the cell reference makes it attainable to pull the system down and fill the opposite channels.
The following two pairs of criterion vary and criterion create a date vary. Every day Site visitors column A holds the dates. The criterion “>=2022-01-01” specifies that the date is larger than or equal to January 1, 2022.
'Every day Site 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 means 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 whole month-to-month site visitors generated by every channel.