How to Sum if Contains an Asterisk

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.

EXAMPLE

How to Sum in Last N Days 1

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

How to Sum in Last N Days 2

Step 2: Press Enter after typing the formula.

How to Sum in Last N Days 3

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.

How to Sum in Last N Days 4

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.

How to Sum in Last N Days 5

We can get the same result.

How to Sum in Last N Days 6

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:

  1. An asterisk (*) means one or more characters.
  2. A question mark (?) means one character.
  3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

  1. “>” – greater than
  2. “<” – less than
  3. “<>” – 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.

How to Sum in Last N Days 7

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.

How to Sum in Last N Days 8

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.

How to Sum in Last N Days 9

So, =TODAY()-2 is equal to 44323.

How to Sum in Last N Days 10

Select “>=”&TODAY()-2 from formula in the formula bar, the press F9, “>=”&44323 is displayed.

How to Sum in Last N Days 11

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.

How to Sum in Last N Days 12

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 to Sum in Last N Days 13

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.

How to Sum in Last N Days 14

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()…

 

Related Posts
How to Sum by Formula if Cell Contains Special Character in Excel
How to Sum by Formula if Cell Contains Special Character 13

In previous article, we have introduced you the method to sum if cell is equal to a certain value, you can check “How to Sum Numbers by Formula if Cells Equal to A Certain Value” on our website for reference. ...

How to Sum by Formula if Cell Contains Both A and B in Excel
How to Sum by Formula if Cell Contains Both A and B in Excel 13

In previous articles, we have introduced the method of sum numbers for cells which are equal to a certain value or are not equal to a certain value (you can refer to ‘How to Sum if Cell Contains Text in ...

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value in Excel

In previous article, we introduce the method to sum numbers for cells are equal to a certain. In this article, we will show you the resolution of ‘sum if cells are not equal to a certain value’ by formula with ...

How to Sum Numbers by Formula if Cells Are Equal to A Certain Value in Excel
How to Sum Numbers by Formula if Cells Equal to A Certain Value 12

We may meet the case that sum numbers based on a certain value in daily work. To sum numbers based on a certain value, we can define this value as a criterion. Then sum up all numbers which meet the ...

How to Sum if Cell Contains Text in Another Column
Sum if Cell Contains Text in Another Column 16

We often sum data based on criteria. To sum values based on a cell, in fact, we sum values based on the text in this cell. If the text is included in another table another column, we need to find ...

How to Sum Data if Between Two Numbers in Excel
Sum Data if Betweens 19

For a set of data, if we want to sum data for a range between two numbers, we need to sum data based on ‘> criteria A and < criteria B’, so there are two criteria here. In our daily ...

How to Calculate Retirement Date and Remaining Years refer to Birth Date in Excel
Calculate Retirement Date 10

Every company has its own police about the retirement date for employees. Suppose employees will be retired from a certain age of 60, how can we calculate the retirement date for everyone base on their birthdays? Actually, there are several ...

How to Sum If Date is Greater or Less Than in Excel
Sum If Date is Greater or Less Than 1

If we sum for certain conditions, we'll use the SUMIF function in excel. For example, count the total sales for a period before of after a specified date. This article will help you to do sum for a specified period ...

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel
highlight cell or row if date8

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional ...

How to Add the Current Month or Year in a Cell in Excel
add current date in cell6

This post will guide you how to add the current month or year into a cell or header or footer in your worksheet. How do I insert the current date or time in a cell with a formula in Excel. ...

Sidebar