Mastering Yearly Summation of Values in Excel

Mastering Yearly Summation of Values in Excel. There are two methods to sum values in Excel based on a specific year in Excel. The first method involves using a helper column, where you add a column with the year number and then sum the values based on the month from that column. The second method involves creating an array by extracting months from the date column and then summing the values based on a year from that array. In this tutorial, we will cover both methods.

Method 1Values in Excel : Using SUMIF with a Helper Column

First, add a new column and use the YEAR function to extract the year number from the dates.

Next, enter the SUMIFS function in the adjacent column. In the SUMIFS function, refer to the year column for the range argument and enter the year you want to sum values for in the criteria argument.

Finally, refer to the quantity column for the sum_range argument and hit enter to get the result.

Method 2 Values in Excel: Using SUMPRODUCT 

The SUMPRODUCT function can be used to create a formula that sums values based on a specific year without the need for a helper column.

First, enter the SUMPRODUCT function and use the double minus sign “–” to convert TRUE and FALSE values in Excel into 1s and 0s, respectively.

Next, use the YEAR function to extract the year value from the date range and compare it to the desired year using an equals sign. Enclose the desired year within parentheses. In the second argument of SUMPRODUCT, refer to the range where you have the values in Excel that you want to sum. Hit enter to get the result.

To understand this formula, break it down into three parts. In the first part, the YEAR function is used to check for the desired year in the date range, resulting in an array of TRUE and FALSE values in Excel.

In the second part, the double minus sign converts TRUE and FALSE values into 1s and 0s, respectively. In the third part, the two arrays are multiplied by each other and the resulting values are summed.

One important thing to note is that when you multiply zero with any value, it returns 0.

Therefore, you are left with only the values in Excel where you have 1 in the first array.