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.

debtor analysisHave 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.

sumif without sum range

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+;

today cell formula

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

with sum if formula

YES! the syntax has changed from the one I discussed above. Well not exactly. Let’s understand this:

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

sum if final

7 COMMENTS

  1. Hasaan,
    I really appreciate you giving these lessons in Excel.
    I use them to help others learn Excel at no charge.
    I have been a fan of yours since 2019 and have only repeated the same lesson approximately 12 times.
    One other thing I personally prefer the written lesson over YouTube. I feel it is better to read, then to just watch it be presented. A person retains by reading, watching a presentation , either on a monitor or television, is the same as watching television for entertainment
    Thank you for your hard work and please continue to enlighten us in Excel, with new lessons

  2. This is the most simple and straight forward excel tutorial I have come across. Thanks.
    Ronald

  3. Excellent support and tips from your end for Excel – appreciate and wait for mails of your to increase my productivity -thxn again

  4. Suhaib Afzal Khan Bangash Suhaib Afzal Khan Bangash

    Zabardast!

  5. Thank you for taking the time to write this information. I really appreciate the examples of how this would be used also. I love learning new things.
    Dina

  6. Hi Hasaan
    So very excellent! Very useful for me. Thanks for sharing.
    Regards
    Lars

  7. Dear Mr. Hasaan,

    Your excel tips are proving beneficial for me. As didn’t know too much about excel but theses tips helped me a lot in my office work.
    Thanks for initiating this.

    Regards
    Haroon

Comments are closed.