In statistic, to create a yearly report for showing the market trend on different years, we often sum a total for different years, so based on the year’s total we can do estimation for the coming year. This scenario frequently occurs at the end of a year. If data for different dates and years are disordered in a table, how can we pick them up by year and sum total for the year correctly only by applying a formula?

To resolve this problem, we can apply Excel’s **SUMIFS**
function and **DATE** function.** SUMIFS** function is used for sum data
after filtering data based on provided criteria. Actually, it can provide
multiple criteria ranges and criteria to filter data, user can sum up data
based on them properly. **DATE** function is used for returning a date form
by the given values or cell reference. Above all, we can combine the two
functions into one formula to sum data by year properly.

In this article, we will show you the formula which can ‘**sum
data by year’ **based on **SUMIFS** function and **DATE** function. To
illustrate **SUMIFS** and **DATE** functions clearly, we will introduce them
with their basic syntax, arguments introduction, simple usage descriptions with
screenshots and explanations. In this article, we will introduce the formula from
inside (**DATE**) to outside (**SUMIFS**), explain each argument in the
formula, also show you the formula’s work process step by step, thus you can understand
it deeply. After reading the following article, I’m sure you can learn well
about **SUMIFS and DATE **functions, and apply them to solve your problem
properly in the future.

**EXAMPLE:**

In the left table, column A lists dates in different years,
column B lists product serial number P00001 and P00002, and column C lists the
amount of different products on different dates and years respectively; in the
right table, column E lists Year information, column F is used for recording
the total amount on each Year. To calculate total amount based on year,
obviously we should filter date by year, and then sum up amount values based on
filtered date. For example, for cell E2 year 2018, firstly we should filter out
date which is in year 2018 in column A, in this case A2, A3, A8 and A9 are
filtered, then sum up C2, C3, C8 and C9. In this instance, to calculate the
total amount for each year, we will apply **SUMIFS** and **DATE**
function to sum data.

**FORMULA APPLICATION**

**Step 1: **In F2, enter the formula **=SUMIFS($C$2:$C$13,$A$2:$A$13,”>”&DATE(E2,1,1),$A$2:$A$13,”<“&DATE(E2,12,31)).**

**Step 2: **Press **Enter** after typing the formula.

In this instance, dates in A2, A3, A8 and A9 belong to year 2018, so amounts in C2, C3, C8 and C9 should be summed. The total equals to 100+84+90+75=349. The formula works correctly.

**Step 3:** Apply the formula to F3 and F4. Just drag the
handle down to apply this formula in F3 and F4 Verify that after dragging, the
formula is updated to **=SUMIFS($C$2:$C$13,$A$2:$A$13,”>”&DATE(E3,1,1),$A$2:$A$13,”<“&DATE(E3,12,31))**
in F3. And value 293 is returned after pressing **Enter**.

Dates in A4, A5, A10 and A11 belong to year 2019, so amounts in C4, C5, C10 and C11 should be summed. The total equals to 60+55+88+90=293. The formula still works correctly.

**Step 4: **Let’s check the formula in F4. The formula is
**=SUMIFS($C$2:$C$13,$A$2:$A$13,”>”&DATE(E4,1,1),$A$2:$A$13,”<“&DATE(E4,12,31))**,
total is 305. It equals to 78+82+75+70.

**FUNCTIONS INTRODUCTION:**

For this formula **=SUMIFS($C$2:$C$13,$A$2:$A$13,”>”&DATE(E2,1,1),$A$2:$A$13,”<“&DATE(E2,12,31))**,
we applied two functions **SUMIFS **and **DATE**.

**DATE FUNCTION:**

**DATE **function can return a serial number that can
represent a particular date if current cell format is ‘General’ or directly
return a date if current cell format is ‘Date’. The syntax is

**DATE(year,month,day)**

All the three arguments **‘year’,’month’,’day’** are
required. Please see some examples below to see the usage of **DATE**
function.

Enter **=DATE(2020,11,12)** in any cell, then 11/12/2020 is returned in date format.

If you want to return a serial number, you can change cell format to ‘**General**’ via **Home->Number ->General**, then a number which can represents a date is displayed instead. Date 11/12/2020 is converted to 44147. On the other side, if you get 44147 after entering **=DATE(2020,11,12)**, you can also select ‘**Date**’ in **Number** dropdown list to convert it to date. Please be aware that, in a formula **DATE** returns a serial number instead of date.

You can also save arguments in different cells, then enter cell reference into **DATE** function, see example below:

If month number is greater 12, for example 14, then 14-12=2, February of the next year will be returned. Date has the same behavior if date is greater than 31. See example below:

**SUMIFS FUNCTION:**

**SUMIFS** function can be seen as **SUM+IFS**, it
supports multiple ‘criteria range’ and ‘criteria’ combinations.

For **SUMIFS** function, it has below arguments:

**SUMIFS(sum_range, criteria_range1, criteria1,
**

[criteria_range2, criteria2]

, …). Criteria range2 and criteria2 are
optional, so it still works if only one criteria range and one criterion exist.
For the usage of **SUMIFS** function, we will split the formula to several
parts, and let you know how this formula works with **SUMIFS** function from
inside to outside.

**HOW FORMULA WORKS:**

**=SUMIFS($C$2:$C$13,$A$2:$A$13,”>”&DATE(E2,1,1),$A$2:$A$13,”<“&DATE(E2,12,31))**

In this formula:

**1.** ‘**$C$2:$C$13**’is the first argument ‘**sum
range**’, it lists all amounts, after filtering data based on provided
criteria, we pick up data from this range to sum up in the last step. As we
also want to apply the formula into other cells, so we add **$** before
column and row to lock the range. You can see that, in former part **Formula
Application** step#3, when copying formula to F3 and F4, this range is fixed.

In formula bar, we can convert range or executed result to actual number or array by pressing **F9** in some cases.** In this case, the sum range $C$2:$C$13 =** **{100;84;60;55;78;82;90;75;88;90;75;70}**

**2.** ‘**$A$2:$A$13**’is the second argument
‘criteria_range1’. It lists dates in different years. We still add **$**
before column and row to lock the range.

**The first criteria range** **A2:A13 =** **{43420;43450;43785;43815;44151;44181;43420;43450;43785;43815;44151;44181}.**

Here we can see that date format is converted to number,
each serial number represents a date. I think you are getting to know why we
use **DATE** function in criteria argument to filter date later.

**3.** **“>”&DATE(E2,1,1)** is the argument
‘**criteria1**’. SUMIFS function supports logical symbols like “>”,”<”
or “=”, we just need to add double quotes “” to enclose them.

DATE(E2,1,1) returns 43101. It represents date 1/1/2018, the start date in year 2018. “>”&DATE(E2,1,1) means date greater than 1/1/2018, in the formula it is “>43101”, which means number greater than 43101.

**4.** ‘**$A$2:$A$13**’is also the fourth argument ‘**criteria_range2**’.

**5.** **“<“&DATE(E2,12,31)** is the
argument ‘**criteria2**’.

DATE(E2,12,31) returns 43465. It represents date 12/31/2018, the end date in year 2018. “<“&DATE(E2,12,31) means date less than 12/31/2018, in the formula it is “<43465”, which means number less than 43465.

Above all, the formula in E2 can be seen as below:

**=SUMIFS({100;84;60;55;78;82;90;75;88;90;75;70},{43420;43450;43785;43815;44151;44181;43420;43450;43785;43815;44151;44181},”>43101″,{43420;43450;43785;43815;44151;44181;43420;43450;43785;43815;44151;44181},”<43465″)**

In this formula, refer to the first group of criteria range
and criteria **({43420;43450;43785;43815;44151;44181;43420;43450;43785;43815;44151;44181},”>43101″**),
number which is greater than 43101 will be saved as True (others are False) in the
array:

**{True,True,True,True,True,True,True,True,True,True,True,True
}**

And refer to the second group of criteria range and criteria
**({43420;43450;43785;43815;44151;44181;43420;43450;43785;43815;44151;44181},”<43465″),**
data which is less than 43465 will be saved in the array.

**{True,True,False,False,False,False,True,True,False,False,False,False}**

Then compare the two arrays and keep ‘True’ for those both are True in two arrays.

**{True,True,False,False,False,False,True,True,False,False,False,False}**

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

**{1,1,0,0,0,0,1,1,0,0,0,0}**

After filtering data by two criteria, make sure that below two arrays are vertically alignment.

**Array1:
{100;84;60;55;78;82;90;75;88;90;75;70} – sum range**

**Array2:
{1,1,0,0,0,0,1,1,0,0,0,0}) – criteria**

Keep current value in array1 if its corresponding value is 1 in array2, otherwise value is changed to 0. You can also think value in array1 multiplies the corresponding value in array2. Thus, we can get another array:

**Array3:
{100;84;0;0;0;0;90;75;0;0;0;0} – sum range**

Sum data in array3, 100+84+90+75=349.

The formula work process is the same for F3 and F4.

### Related Functions

- Excel DATE function

The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)… - Excel YEAR function

The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…