Sumifs not blank google sheets

The most primitive way to total cells in Google Sheets is to add the cell references to the formula and put plus [+] signs between them. For example:

=B1+B2+B3+B4+B5

For longer ranges of cells, such as an entire column or row, this solution is not handy at all. Instead, you should use the SUM function or one of its derivatives: SUMIF and SUMIFS. Let’s check out how the native functions work and which use cases are a fit for them. 

Google Sheets SUM to total values

SUM is a Google Sheets function to return a total of numbers or cells, or both specified numbers and cells. The SUM syntax has several variations depending on what you’re going to total.

Google Sheets SUM syntax to total values

=SUM[value1,value2,value3,…]

• value is a numeric value

Google Sheets SUM basic formula example 

=sum[1,2,3,4,5]

Google Sheets SUM to total a cell range

Google Sheets SUM syntax to total cells

=SUM[cell-range]

• cell-range is the range of cells to total. The range can be specified using commas for scattered cells like A1,B2,C3, or a colon for integral cell ranges like A1:A100. 

Google Sheets SUM formula example for scattered cells

=sum[B1,B3,C2,D1,D3]

Google Sheets SUM formula example for an integral cell range

=sum[B1:B5]

Google Sheets SUM to total values and cells

Google Sheets SUM function syntax to total values and cells

=SUM [value1, value2,...,cell-range]

Google Sheets SUM formula example to total values and cells

=sum[1,2,B3,B4:B5]

How to SUM a column in Google Sheets

Now you know the syntax of the SUM formulas, so let’s check out how they work on real-life data. We’ve imported some records from HubSpot to Google Sheets using Coupler.io.

Coupler.io is a solution for importing data from different apps such as Xero, Airtable, Pipedrive, and other sources to Google Sheets, Microsoft Excel, and Google BigQuery. The tool allows you to not only export data but also automate dataflows on a custom schedule to optimize your reporting and analytics.

So, with the data imported to Google Sheets, we can proceed to SUM formula examples.

SUM a limited range from a column in Google Sheets

If you need to return the total of a limited range from a column, e.g. B2:B182, a simple SUM syntax will do:

=sum[B2:B182]

If you want to return the total in the bottom of your column range, type the equal sign [=], and Google Sheets will suggest you the SUM formula itself.

Note: This hack works only within 5 rows below the last value in a column.

SUM an entire column in Google Sheets

If you need to total an entire column, specify the column range as follows:

=sum[B2:B]

Now, every new value within the column will be added to the total value, so you won’t have to manually tweak the SUM formula. 

How to SUM a row in Google Sheets

For this use case, we took a Profit and Loss report that we imported using the Xero Reports to Google Sheets integration. Now, let’s use SUM to total values from row 2.

Here is the SUM formula to total the row values:

=SUM[C2:G2]

How to return SUM of multiple rows in one column in Google Sheets

The SUM formula above worked well, but is it possible to expand it to other rows using ARRAYFORMULA?

Unfortunately, SUM + ARRAYFORMULA doesn’t expand: =ARRAYFORMULA[SUM[C2:G2]] will give a single number. 

But there is a workaround.

Workaround#1: Sum multiple rows with ARRAYFORMULA in Google Sheets

With this workaround, you don’t need the SUM function at all. The idea is to manually add the columns and use ARRAYFORMULA to expand the results. Here is the formula for our case:

=arrayformula[if[len[C2:C]=0,,[C2:C+D2:D+E2:E+F2:F+G2:G]]]

Note: if[len[C2:C]=0 is needed to remove “0” for empty rows. 

Read our Guide of Using ARRAYFORMULA in Google Sheets.

Workaround#2: Sum multiple rows with ARRAYFORMULA, MMULT, TRANSPOSE, and COLUMN in Google Sheets

For this workaround, we’ll need to nest four functions: ARRAYFORMULA, MMULT, TRANSPOSE, and COLUMN. Here is how the formula looks:

=ARRAYFORMULA[MMULT[C2:G23,TRANSPOSE[COLUMN[C2:G23]^0]]]

MMULT is an array function to multiply matrices. Our first matrix is C2:G23. The second matrix must have the number of rows equal to the number of columns in the first matrix – five, in our case. This is how it should look:

Instead of manually tailoring the matrix we need, we’ll use the combination of two functions, TRANSPOSE and COLUMN, in the following way: TRANSPOSE[COLUMN[C2:G23]^0]

In the end, wrap up everything with ARRAYFORMULA.

The drawback of this workaround is that you have to specify the exact range of columns. 

How to return SUM of multiple columns in one row in Google Sheets

Since we got the total column, let’s get the total row beneath the data set as well. MMULT nested with ARRAYFORMULA, TRANSPOSE, and ROW will help with that. Here is how it looks:

=TRANSPOSE[ARRAYFORMULA[MMULT[TRANSPOSE[C2:H23],[ROW[C2:H23]^0]]]]

Let’s wrap up with SUM for now, since we have more interesting cases with SUMIF and SUMIFS.

Google Sheets SUMIF to sum a data range on a condition 

SUMIF is a Google Sheets function to return a total of cells that match a single specific criterion. Put simply, the SUMIF function filters the range according to the specified criteria and sums values based on this filter. The syntax is the same as SUMIF Excel.

Google Sheets SUMIF syntax

=SUMIF[criterion-range,"criterion",range-to-sum]

• criterion is a condition to filter data. criterion can be a number, a text string, a cell reference, as well as a variety of conditions, such as greater than or equal to.
• criterion-range is a data range to examine based on the criterion
• range-to-sum is a data range with values to total. This parameter is optional and can be omitted in cases when criterion-range is the range with values to total.

Now, let’s check out SUMIF formula examples with different criteria cases. For this, we’ll use a dummy data range that was already featured in the blog post COUNTIF vs. COUNTIFS. It’s an extract of a sandwich store’s database, in which you can see Product, Price, Quantity, and other columns. By the way, this data set was imported from Airtable to Google Sheets.

Google Sheets SUMIF by a logical expression criterion: greater, less, or equal

Use one of the following logical operators to build a criterion for the SUMIF formula:

function sumbycolor[sumRange,colorRef] {
  var activeRg = SpreadsheetApp.getActiveRange[];
  var activeSht = SpreadsheetApp.getActiveSheet[];
  var activeformula = activeRg.getFormula[];
  var countRangeAddress = activeformula.match[/\[[.*]\,/].pop[].trim[];
  var backGrounds = activeSht.getRange[countRangeAddress].getBackgrounds[];
  var sumValues = activeSht.getRange[countRangeAddress].getValues[]; 
  var colorRefAddress = activeformula.match[/\,[.*]\]/].pop[].trim[];
  var BackGround = activeSht.getRange[colorRefAddress].getBackground[];
  var totalValue = 0;
  for [var i = 0; i < backGrounds.length; i++]
    for [var k = 0; k < backGrounds[i].length; k++]
      if [ backGrounds[i][k] == BackGround ]
        if [[typeof sumValues[i][k]] == 'number']
          totalValue = totalValue + [sumValues[i][k]];
  return totalValue;
};

=SUMBYCOLOR[range-to-sum,colored-cell-criterion]

=sumbycolor[H2:H,H2]

=SUMIFS[range-to-sum,criterion-range1,"criterion1",criterion-range2,"criterion2",...]

=sumifs[H2:H,E2:E,"*cheese*",G2:G,"14",H2:H]

=SUM[ARRAYFORMULA[SUMIF[criterion-range, {"criterion1", "criterion2", "criterion3"}, range-to-sum]]]

=sum[arrayformula[sumif[G2:G, {"=13","14"}, H2:H]]]