How to Sum Data if by Year in Excel

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:

Sum Data if by Year

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

Sum Data if by Year

Step 2: Press Enter after typing the formula.

Sum Data if by Year

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.

Sum Data if by Year

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.

Sum Data if by Year

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.

Sum Data if by Year

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.

Sum Data if by Year

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

Sum Data if by Year

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:

Sum Data if by Year

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}

Sum Data if by Year

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

Sum Data if by Year

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.

Sum Data if by Year

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

Sum Data if by Year

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.

Sum Data if by Year

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.

Sum Data if by Year

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)…
Related Posts

How to Use COUNTIFS function with Multiple Criteria and OR Logic in Excel
countifs function with multiple criteria or logic4

This post will guide you how to Use COUNTIFS function with multiple criteria and OR logic in Excel 2013/2016 or Excel office 365. How to count with multiple criteria and OR logic in Excel. COUNTIFS with Multiple Criteria and OR ...

How to Count Numbers that begin with Specific Value  in Excel

This post will guide you how to count numbers that begin with a specific value or number in Excel 2013/2016 or Excel office 365. How do I count the number of values that begin with a given specific value by ...

How to Convert Fahrenheit to Celsius or Kelvin in Microsoft Excel
convert fahrenheit to celsius3

This post will guide you how to Convert Temperatures among Fahrenheit, Celsius, and kelvin using a formula in Microsoft Excel. How do I convert Fahrenheit to Celsius in Excel 2013/2016. How to convert degrees Fahrenheit to degrees Celsius in Excel. ...

How to Change the Orientation of Worksheet in Excel
change orientation of worksheet1

This post will guide you how to change the default Portrait orientation to Landscape page orientation in your worksheet in Excel. Change the Orientation of Worksheet When you have a list of data with lots of columns, you can try ...

Limit Formula Result to Maximum or Minimum Value
limit formula result2

This post will guide you how to limit formula result to a maximum or minimum value in excel. For example, if you want to limit the results of a formula to a maximum value of 200, how to this this ...

Insert or Delete Comment using Shortcut
insert delete comments1

This post will guide you how to use shortcut to insert or delete a comment that exists in the active cell in excel. How do I quickly insert or delete a comment in an active cell with shortcut keys in ...

Excel SWITCH Function

This post will guide you how to use Excel SWITCH function with syntax and examples in Microsoft excel. Description The Excel SWITCH function compares one value against a list of values, and returns the result corresponding to the first matching ...

Excel ROUNDUP Function
excel roundup examples5

This post will guide you how to use Excel ROUNDUP function with syntax and examples in Microsoft excel. Description The Excel ROUNDUP function rounds the number up to a specified number of decimal places. It will round away from 0. ...

Fill Blank Cells with specific value (0 or null)
fill empty blank cell with value2

This post will teach you how to fill all blank cells with specific value, such as: 0 or any other values in excel. How to locate all blank cells and then fill in all the blanks with any specific value. ...

contact us
Nested IF function alternatives1

    Sidebar