The Excel HelpDesk Support team received the following request for help:
Problem – Unable to count total deposits by month
I need a count if function to count cells which returns a date in the current month. We receive deposits on lots, column A is lot no. and Column B is date deposit paid. Refer to the attached sheet – I need formulas for the highlighted yellow cells. Thank you 🙂
What we want basically here is to produce a report that includes the count of the total number of deposits for a particular month. However, the COUNTIF function will not accomplish the result we need. Instead, we will be using the powerful SUMPRODUCT function.
The above formula will give us the total deposits for the month of April. Check the actual screenshot of the document below:
The formula just works without the need of VBA coding. To get the total deposits for the month of May, change the number 4 (April) to 5 (May).
Now to explain each of the functions we used to understand it better. We have 3 functions here namely SUMPRODUCT, N, and MONTH.
The MONTH function
Let’s start with the most inner function we used which is MONTH. This function returns the number of month of the given date. Below are examples using the actual data above:
=MONTH(B2) will return 5 since value of B2 is 1-May
=MONTH(B4) will return 6
The N function
This function converts Boolean value to 1 if TRUE, and 0 if FALSE. We will need this because the SUMPRODUCT does not recognize non-numeric values such as TRUE or FALSE. Logically, we can only get the sum if we have 1+1+0, not TRUE+TRUE+FALSE.
=N(MONTH(B4)=6) will return 1 since the month is June
=N(MONTH(B4)=5) will return 0
The SUMPRODUCT function
SUMPRODUCT returns the sum of products of corresponding ranges or arrays. The syntax is:
The function accepts arrays or lists which are usually range of cells. To have an overview of the function, take a look at the example below.
With the above example, we put two sets of lists, B3:B6 which contains 3, 2, 3, and 5, and second list is C3:C6 which is 4, 4, 3, and 2. What the function does is to get the sum of the products, so (3*4) + (2*4) + (3*3) + (5*2) is equal to 39.
Going back to our main solution:
To summarize how it works, first it checks the dates in the range we defined, which is B$2:B$10, if the month of the date is April (The dollar sign you see in the range simply tells Excel that it should not be changed when copied to other cells). If the month is April, then it returns TRUE, if not then it returns FALSE. When we have the value TRUE or FALSE, the N function converts it to 1 or 0 so it can be recognized by the SUMPRODUCT formula. So we have now sets of 1’s and 0’s that are summed up by the SUMPRODUCT function.
Lastly, in order to get the total deposits of the current month, we use the following formula:
NOW() returns the current system date, hence, MONTH(NOW()) indicates that we are obtaining the current month. Download the sample workbook used for this post.
|Download Sample Workbook Here|
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.