The way to Use SUMIF, SUMIFS in Google Sheets

0
54


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.

Screenshot of Google Sheet showing SUMIF example

The information import resulted in 748 rows of channel-traffic totals.

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?

SUMIF

The imported site visitors data has three columns: date, channel, and the variety of distinctive visits for every channel on that day.

Screenshot of Google Sheet with SUMIFs examples

The supply knowledge has three columns in a sheet: Date, Channel, and Web site Site visitors.

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.”

Screenshot of Google Sheets SUMIFs example with two columns

A brand new sheet, Whole Site visitors by Channel, incorporates the site visitors totals for every channel for your complete interval.

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.”

=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’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.

Screenshot of Google Sheets showing SUMIF being typed

Google Sheets suggests SUMIF even earlier than the typing is full.

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.

Screenshot of Google Sheet with the suggested SUMIF function.

Deciding on the steered SUMIF perform simplifies the formula-creation course of.

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.

Screenshot showing the sum range as the criterion.

Clicking on the A3 cell provides its worth, “Affiliate,” to the SUMIF system because the criterion.

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.

Screenshot showing Column C as the sum range.

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.

Google Sheets screenshot showing the total visits from affiliates.

The SUMIF system labored. The affiliate channel drove 53,875 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.

Screenshot showing the effect of dragging a cell

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 widespread for ecommerce websites.

SUMIFS

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.

Screenshot of Monthly Traffic by Channel

The Month-to-month Site visitors by Channel sheet is the place the SUMIFS perform will get month-to-month site visitors totals.

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")
Screenshot of a SUMIFs formula.

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 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.

Screenshot of the final Daily Traffic sheet showing the SUMIFs formula

The SUMIFs perform was simple to get month-to-month totals by channel.

LEAVE A REPLY

Please enter your comment!
Please enter your name here