What is the sum of all the dates of April
Answers
Answer:
Manytimes, we want to calculate some values by month. Like, how much sales was done in a perticular month. Well this can be done easily using pivot tables, but if you are trying to have a dynamic report than we can use a SUMPRODUCT or SUMIFS formula to sum by month.
0013
Let’s start with SUMPRODUCT solution.
Here is the generic formula to get sum by month in Excel
=SUMPRODUCT(sum_range,--( TEXT(date_range,"MMM")=month_text))
Sum_range : It is the range that you want to sum by month.
Date_range : It is the date range that you’ll look in for months.
Month_text: It is the month in text format of which you want to sum values.
Now let’s see an example:
Example: Sum Values by Month in Excel
Here we have some value associated with dates. These dates are of Jan, Feb, and Mar month of year 2019.
0014
As you can see in the image above, all the dates are of year 2019. Now we just need to sum values in E2:G2 by months in E1:G1.
Now to sum values according to months write this formula in E2:
=SUMPRODUCT(B2:B9,--(TEXT(A2:A9,"MMM")=E1)))
If you want to copy it in adjacent cells then use absolute references or named ranges as you in the image.
0015
This gives us the exact sum of each month.
How it works?
Starting from the inside, let’s look at the TEXT(A2:A9,"MMM") part. Here TEXT function extracts month from each date in range A2:A9 in text format into an array. Translating to formula to =SUMPRODUCT(B2:B9,--({"Jan";"Jan";"Feb";"Jan";"Feb";"Mar";"Jan";"Feb"}=E1))
Next, TEXT(A2:A9,"MMM")=E1: Here each month in array is compared with text in E1. Since E1 contains “Jan”, each “Jan” in array is converted to TRUE and other into FALSE. This translates the formula to =SUMPRODUCT($B$2:$B$9,--{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE})
Next --(TEXT(A2:A9,"MMM")=E1), converts TRUE FALSE into binary values 1 and 0. The formula translates to =SUMPRODUCT($B$2:$B$9,{1;1;0;1;0;0;1;0}).
Finally SUMPRODUCT($B$2:$B$9,{1;1;0;1;0;0;1;0}): the SUMPRODUCT function multiplies the corresponding values in $B$2:$B$9 to array {1;1;0;1;0;0;1;0} and adds them up. Hence we get sum by value as 20052 in E1.
Step-by-step explanation: