Using SUMIF function with Dates in Excel | Microsoft Excel 2021 || Excel Tips And Tricks ||

 

Using SUMIF function with Dates in Excel

I was in the middle of making an accounting project where I had to use SUMIF. This wonderful function that everyone must know especially accountants. In many instances SUMIF can be used as an alternative to VLOOKUP function and it is much easier to use as well. However, using this formula with dates is a bit different and many find it broken with they use cell references containing dates.

Have a look at the sample data at the right pertaining to five customers to whom sales on credit were made with the invoice due dates given in column B. Now having the information given to an excellent accountant, manager asks to sum the invoices that are past due on today’s date.

Now in our case as the data is too small we can easily do that with a calculator but imagine you have a large file containing 5000+ transactions. In such situations we will do what every excellent accountant does i.e. use SUMIF function.

Let’s understand briefly the syntax of this formula and how it works and then we will understand how it is going to help us in our example.

=SUMIF(range, criteria, [sum_range])

Range: part of your data that you want to check up against a criteria. As it is a range so most of the time it is multiple of cells but does not mean that it needs to be a single row or single column you can select multiple row and multiple columns as range.

Criteria: This is the logical test you want to perform e.g. equal to or less than or greater than or combination of these logic. One important bit about this is that logic can be used with numbers, text or other forms of expressions including cell references.

Sum_range: This is an optional piece in the formula and that is the reason it is surrounded by square [] brackets. This is the range of cells which actually gets added up if the content in “Range” matches the “Criteria”. However, as this is optional and if it is omitted from the formula then the “Range” will work as both “Range” and “Sum_range” i.e. first range will be checked against criteria and then the same will be added up. Take a look at the picture at the right to understand better.

Back to our case now. We need two things:

• Today’s date: For this we will use “=TODAY()” function
• Sum all the invoices that are already due: for this we will use SUMIF function by referring to cell that contains today’s date as a criteria and finds out the invoices past due and then sum them.

Step 1: Put today’s date in the cell you desire (in our case it is C8) by typing =today() and press enter. Yes just that and excel will put current date in cell. You can also put today’s date using a shortcut Ctrl+;

Step 2: Write SUMIF formula in the cell where you want the sum by typing formula: =SUMIF(B2:B6,”<“&C8,C2:C6)

or SUMIF formula your criteria needs to be in double quotes. If your criteria is a hard number or a hard text you can put that number or text with logical test inside double quotes. However, if you need to refer to a cell then you have to put & (ampersand) with the cell address to let excel know that this is connected with the criteria. Without having criteria in quotes and putting & with the cell address function will not work.

Once done with punching in the formula press enter and you will get the total of invoices that are past due as in the following picture.