In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to sum numbers based on criteria, we can SUMIF or SUMIFS function, compare with SUMIF function, SUMIFS can handle multiple criteria effectively.
In this article, we will provide a simple case to show you the way we use SUMIFS function to sum amounts for different items in last 3 days. We will introduce you the syntax, arguments of SUMIFS function, create a formula to sum numbers per our demand. We will also split the formula to several parts and let you know how the formula works step by step. After reading the article, you may have a simple understanding about SUMIFS function and you can use it to sum in last n days.
Table of Contents
EXAMPLE
Refer to the left-hand side table, we can see some kinds of fruits and their amounts on different dates are listed, total three columns. Based on dates, we can see there are four groups of “Apple, Orange and Banana”.
In the right-hand side table, in “Fruit” column, we list the three kinds of fruits in the same column but different cells. And in the adjacent “Total” column, total amount will be calculated and listed in cells properly for each fruit accordingly. We need to enter a formula into F2 to calculate total amount automatically. As we want to just create one formula which also can be applied in cell F3 and F4 directly, so we need the formula can adjust cell reference and range reference automatically and return the correct result based on adjusted references properly.
As we want to sum amounts for different fruit, we have criteria “Fruit=Apple/Orange/Banana” now; Besides, if we want to sum amounts for each fruit in a specific period, for example only sum amounts in last 3 days (today, yesterday and the day before yesterday, current date is 9-May-21 which is on the top in Date column), we will have new criteria “in last 3 days” now. To create a formula to resolve this issue, we can apply SUMIFS function here as it can hand the case with multiple criteria.
FORMULA – SUMIFS FUNCTIONS
Step 1: In F2, enter the formula =SUMIFS($C$2:$C$13,$B$2:$B$13,”>=”&TODAY()-2,$A$2:$A$13,$E2).
Step 2: Press Enter after typing the formula.
We can see $15,000.00 is returned. The format is correct. Actually, if you didn’t set the format for cell F2 before, only “15000” is returned. Before entering the formula, you need to focus on this cell, and click ‘Dollars’ icon under ‘Number’ section, and also double click on ‘Increase Decimal’ icon to add two decimal places for the returned number.
Now, let’s check the result. in column A, only cell A2, A5, A8 and A11 can meet our criteria “Fruit=Apple”, as we only sum the amounts in last 3 days, so A11 is ignored. We just need to sum amounts from C2, C5 and C8, so the total number is 5000+6000+4000=15000. The formula works correctly.
By the way, if you are confused with cell reference and range refence in the formula, we can also define range name before creating the formula. See steps below:
1. Select A2:A13, then in Name Box enter “Fruit”, press Enter Define reference B2:B13 as “Date”, C2:C13 as “Amount”. See screenshot below.
2. Then create the formula =SUMIFS(Amount,Date,”>=”&TODAY()-2,Fruit,$E2) in F2. You can enter “Amount” directly in the formula instead of selecting a range reference.
We can get the same result.
FUNCTION INTRODUCTION
SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
For SUMIFS function, is supports wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators like “>”,”<”. If wildcards or logical operators are required, they should be enclosed into double quotes (““).
The usage of wildcards:
- An asterisk (*) means one or more characters.
- A question mark (?) means one character.
- The position of asterisk or question mark means the character(s) position relative to the entered part.
The usage of logical operators:
- “>” – greater than
- “<” – less than
- “<>” – not equal to
Today function can return current date. It doesn’t have any argument.
ALL ARGUMENTS
The formula is =SUMIFS($C$2:$C$13,$B$2:$B$13,”>=”&TODAY()-2,$A$2:$A$13,$E2). Refer to above SUMIFS function introduction, we will split it to several parts.
SUMIFS – SUM RANGE
$C$2:$C$13 is the ‘sum range’ in this case.
We add $ before row and column index to make this range as absolute references. When applying the formula to other cells, this range is locked.
Select $C$2:$C$13 from formula in the formula bar, the press F9, values in this range are expanded in an array.
SUMIFS – CRITERIA RANGE1
$B$2:$B$13 is the first criteria range. We have two criteria ranges in this instance. The first one provides date criteria.
Select $B$2:$B$13 from formula in the formula bar, the press F9, values in this range are expanded in an array.
We found that dates are converted from Date format to General format: {44325;44325;44325;44324;44324;44324;44323;44323;44323;44322;44322;44322}. Each five digits number represents a date.
SUMIFS – CRITERIA1
“>=”&TODAY()-2 is the criteria1.
As we want to sum amounts in last 3 days, except today, we only need to sum amounts on yesterday and the day before yesterday, TODAY()-2 can represent the day before yesterday, and we use “>=” to make sure this day is included in calculation.
Logical operator “>=” is quoted in “”;
Today() returns the number which can represent current date; Actually TODAY function will return current date in Date format, but we can change it to General format by selecting General in Number Format dropdown list in Number section.
So, =TODAY()-2 is equal to 44323.
Select “>=”&TODAY()-2 from formula in the formula bar, the press F9, “>=”&44323 is displayed.
SUMIFS – CRITERIA RANGE2
$A$2:$A$13 is the second criteria range. The second one provides fruit references.
Select $A$2:$A$13 from formula in the formula bar, the press F9, values in this range are expanded in an array.
SUMIFS – CRITERIA2
$E2 is the criteria2.
In E2, one kind of fruit “Apple” is recorded. In this case, we can omit $ actually as formula is only applied in F3 and F4 which are in the same column with F2. But if we want to copy this formula in other cells in different horizontal levels, column E will be adjusted in the formula automatically due to position changed, so we can add $ before column to lock it.
Select $E2 from formula in the formula bar, the press F9, “Apple” is displayed.
HOW THE FORMULA WORKS
After explaining each argument in the formula, now we will show you how the formula works with these arguments.
After expanding values in each range reference, in the formula bar, the formula is displayed as:
=SUMIFS({5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000},{44325;44325;44325;44324;44324;44324;44323;44323;44323;44322;44322;44322},”>=”&44323,{“Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”},”Apple”)
There are two pairs of criteria range and criteria.
{44325;44325;44325;44324;44324;44324;44323;44323;44323;44322;44322;44322} – Criteria Range1
“>=”&44323 – Criteria1
And
{“Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”} – Criteria Range2
“Apple” – Criteria2
If values from each criteria range are matched with the criteria, “True” will be recorded and saved, otherwise, “False” will be saved instead. So, after comparing, we can get a new criteria range:
{True;True;True;True;True;True;True;True;True;False;False;False} – after comparing with criteria1
{True;False;False;True;False;False;True;False;False;True;False;False} – after comparing with criteria2
For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above two arrays are converted to below arrays which consist of numbers “1” and “0”.
{1;1;1;1;1;1;1;1;1;0;0;0} – criteria range1
{1;0;0;1;0;0;1;0;0;1;0;0} – criteria range2
As we sum range need to meet the two criteria both, so we just keep the intersection of the two arrays. If in the same position, 1 is recorded for both, keeps 1, otherwise keeps 0.
{1;0;0;1;0;0;1;0;0;0;0;0} – the intersection of the two criteria range
Now, we have below arrays:
{5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000} – sum range
{1;0;0;1;0;0;1;0;0;0;0;0} – criteria range
Elements are one-to-one matched in two arrays. Multiply the two elements in the same position in the array. Then we can get a new array.
{5000;0;0;6000;0;0;4000;0;0;0;0;0}
Add all products in above array, we get 15000.
Now, drag down the formula to fill F3 and F4. We can get total amount in last 3 days for each kind fruit properly.
SUMMARY
1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.
2. It supports user defined range name.
3. It supports wildcard.
4. It supports logical operators.
5. TODAY function only returns current date. It has no argument.
Related Functions
- Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])… - Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)… - Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…) - Excel TODAY function
The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…