SUMIF() function sums up a numerical range given criteria from another range of equal dimension.
The criteria can test whether the range equals to a certain text string, greater or smaller than a number, between certain dates etc. You can also use wildcards for fuzzy match.
SUMIF() is one of the most used functions in Excel and definitely worth mastering if you are learning Excel. Its cousin, SUMIFS(), allows more complicated criteria.
Google Sheets SUMIF and Excel SUMIF
Whether you are doing SUMIF() function on Excel or Google Sheets, the formulas are the same.
=SUMIF(range, criteria, [sum_range])
range – Required. This is the range where criteria shall be applied.
criteria – Required. This tells the program which cells in sum_range should be added
sum_range – Optional. This is the range to sum.
We will go through SUMIF() usage in more details below using examples.
SUMIF Equal to Cell
This is the simplest case where criteria is a value in a cell.
Suppose we want to know the total quantity in stock for vegetables where there is a discount.
The formula in cell H5 is
=SUMIF(tblStock[Discount],$H$4,tblStock[Quantity in Stock])
where H4 contains the word “Yes”
If you do not intend to drag the formula to other cells, then you do not need dollar signs ($) in front of the address range.
SUM Cells If Text Equals…
You can check in criteria argument whether the range argument contains certain text.
You then enclose the text in double quotes.
Again, we are after the quantity in stock for vegetables where there is a discount.
In cell G9, the formula is
=SUMIF(tblStock[Discount],”Yes”,tblStock[Quantity in Stock])
SUMIF Wildcard Search
SUMIF() formula supports wildcard symbols for near match search.
“?” stands for one character, and “*” for multiple characters.
Suppose we want to sum Quantity in Stock for all Vegetables where Vegetable name starts with “P”
In cell G13, the formula is
=SUMIF(tblStock[Vegetables],”P*”,tblStock[Quantity in Stock])
If you want where Vegetable names contain the character “al”, you would change criteria argument to “*al *”
SUMIF Between Two Values
The best way to sum between two values is to use SUMIFS() function instead.
SUMIFS function has the syntax:
=SUMIFS(sum_range, criteria_range, criteria_1, criteria_range, criteria_2…)
Notice that in SUMIFS(), sum_range is the first argument, not the last.
Criteria_range and criteria arguments come in pairs. Criteria argument defines what criteria to apply to criteria_range. You can specify multiple criteria ranges.
Scenario: To sum Quantity in Stock for all Vegetables where Quantity in Stock is between 5 and 7
Cell G17’s formula is
=SUMIFS(tblStock[Quantity in Stock], tblStock[Quantity in Stock],”>=5″,tblStock[Quantity in Stock],”<=7″)
SUMIF Date Range
If you want to sum a numerical column based on date ranges in other columns, you can either put the dates with the logical operators (<, >, =) together inside double quotation marks or use an ampersand (&) sign to connect them.
As an example, suppose you want to sum Quantity in Stock for all Vegetables where Import Date is on or after 5th Jan, and Promotion Date is before 7th Jan.
Note that in this example, the dates are from two different columns. The more common example is where the date range comes from a single column.
=SUMIFS(tblStock[Quantity in Stock], tblStock[Import Date],”>=5/1/2020″,tblStock[Promotion Date],”<7/1/2020″)
=SUMIFS(tblStock[Quantity in Stock], tblStock[Import Date],”>=”&D21,tblStock[Promotion Date],”<“&E21)
where cell D21 contains the value Jan 5th 2020, and cell E21 the value Jan 7th 2020.
SUMIF Date Range Not Working
Check that the formula is constructed correctly and that the date column you are referring to are formatted as dates.
This post addresses most common issues you would face when using SUMIF. Plenty of examples are given along with a quick debug of using SUMIF() with date ranges.
It is worth spending time learning about SUMIFS() to work with multiple conditions.