TODAY and COUNTIF: Count last month’s orders
Why only sums? Using the TODAY function together with the COUNTIF function, you can perform date-based conditional counts too.
For example, in the image below, we have multiple orders placed on different dates📜
The date today remains the same i.e. 25 March 2023, and we want to count the orders placed in the last 30 days only. To do that:
- Write the TODAY function as follows.
= TODAY ()
This gives us the date today i.e. 25 March 2023.
- Write the following formula to deduct 30 from it. That’s because we want to count the orders from the last 30 days only.
= TODAY () – 30
We get a date 30 days before 25 March 2023📝
- Now write the COUNTIF function as follows:
= COUNTIF (
- Define the range as the first argument
= COUNTIF (A2:A7
This is the range against which the criteria will be checked. We are setting it to the cell range that contains the dates i.e. A2:A7.
- Define the criteria using a greater than operator and the TODAY function above.
= COUNTIF (A2:A7, “>” & TODAY () – 30)
This tells Excel to see if each date in the range A2:A7 is greater than “today’s date less 30 days”. If it is, the COUNTIF function will count it in.
- Hit Enter to get the results.
The answer is 4. How did that come?
Excel has counted only the first 4 dates. Only these dates fall 30 days behind today i.e. between 23 February 2023 and 25 March 2023.
All orders placed on these dates meet our criteria and are counted in🧐