Learn how to Use SUMIF, SUMIFS in Google Sheets
6 mins read

Learn how to Use SUMIF, SUMIFS in Google Sheets


Google Sheets SUMIF and SUMIFS capabilities assist analyze, manage, and sum information primarily based on particular standards.

These formulation complete cell values after they meet the necessities you set.

I just lately did this for an internet enterprise that wished to dig deep into its site visitors studies.

I exported the corporate’s every day web site site visitors by channel from its analytics software program and imported that information right into a Google Sheet, which I saved as “Day by day Site visitors.”

I then aggregated the info: one row per channel per day. The corporate receives 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 info to kind by means of manually.

Screenshot of Google Sheet showing SUMIF example

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

Utilizing pattern information, let’s take a look at how the SUMIF and SUMIFS formulation may also help analyze web site site visitors, particularly:

  • How a lot site visitors did every channel generate for the complete 87-day interval?
  • How a lot did every channel generate month-to-month?

SUMIF

The imported site visitors information 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 information has three columns in a sheet: Date, Channel, and Web site Site visitors.

My first job is to determine how a lot site visitors every channel generated for the complete 87 days.

To start, I created a sheet referred to as “Complete Site visitors by Channel,” with two columns: “Channel” and “Complete Site visitors.”

Screenshot of Google Sheets SUMIFs example with two columns

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

I used the SUMIF system to acquire the entire site visitors from every channel for the complete date vary. The system accepts two or three parameters.

The 2-parameter model contains “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 sort “=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 advised SUMIF system, Google Sheets confirmed me a information. I can navigate across the workbook and choose columns from the Day by day Site visitors sheet as wanted.

Screenshot of Google Sheet with the suggested SUMIF function.

Choosing the advised SUMIF operate simplifies the formula-creation course of.

I navigate to the “Day by 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, Complete 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 Day by 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 entire.

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

Right here is the finished system.

=SUMIF('Day by day Site visitors'!B:B,A3,'Day by day Site 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.

Google Sheets screenshot showing the total visits from affiliates.

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 all the way 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 operate is just like SUMIF, besides it permits a number of standards. This characteristic 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 potential 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 operate will get month-to-month site visitors totals.

I’ve labored by means of the SUMIFS operate in the identical method because the SUMIF operate. As a result of it might probably embody many standards, the SUMIFs system can get lengthy. Ultimately, I’ve the finished system for the affiliate channel in January 2022.

=SUMIFS('Day by day Site visitors'!C:C,'Day by day Site visitors'!B:B,A2,'Day by day Site visitors'!A:A,">=2022-01-01",'Day by 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 Day by day Site visitors sheet. It incorporates the precise site visitors quantity.

=SUMIFS('Day by day Site visitors'!C:C,

The subsequent two comma-separated parameters are the primary criterion vary and the primary criterion.

'Day by day Site visitors'!B:B,A2

Column B within the Day by day Site visitors sheet (‘Day by day Site visitors’!B:B within the system) is the listing of channels. Cell A2 holds the channel identify “Affiliate.” I might have typed “affiliate,” however utilizing the cell reference makes it potential to pull the system down and fill the opposite channels.

The subsequent two pairs of criterion vary and criterion create a date vary. Day by 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.

'Day by 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 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 operate made the method comparatively easy. I now know the entire month-to-month site visitors generated by every channel.

Screenshot of the final Daily Traffic sheet showing the SUMIFs formula

The SUMIFs operate was easy to get month-to-month totals by channel.

Leave a Reply

Your email address will not be published. Required fields are marked *