Best Online Learning Platform and Collect Information

How to count cells between dates COUNTIFS Function example

To count cells between dates we can use CountIfs formula. Please read below for detailed examples and explanation. For better understanding, we have created a case over here. Let's assume that you have a purchase sheet of office inventory, and you need to count that how many products have been purchased in a year. See the image below.

For the flawless results, you must know these excel functions or methods. If you are not aware any of these take a quick look by clicking on.

  1. Date Function.
  2. Concatenate Function.
  3. Freeze cells Method. 


Count cells between dates CountIfs function example

Here is the Generic formula syntax for CountIfs:

=COUNTIFS(range,">=" &date1,range,"<=" &date2)

Formula Explanation:- To count the number of cells between two dates, we can use the COUNTIFS function.  In the example shown, G5 contains the formula.
According to the formula, the formula for the current data will be as mentioned below.

=COUNTIFS($D$5:$D$20,">="&DATE(F6,1,1),$D$5:$D$20,"<="&DATE(F6,12,31))

This Formula Counts Product purchased in the period of one year. We have used freeze cells ($$) method for flawless results.

Step by step formula explanation:

The Countif Function is used to count the number of cells that meet multiple criteria. In this case, we need to provide two criteria. (1. One criteria for the earlier date and 2. One for the later date). We have used Purchase Dates columns as a Criteria for both. And for both criteria ranges, we have chosen the first date of a year and the last date of the same year.

Most commonly used Excel formulas.

How to use Vlookup in Excel.
How to import data from the web into Excel.
How To Make Shared Excel File And Allow To Edit By Multiple Users at one time.

Thanks For reading, I hope you got your problem. Please like and share this article on facebook if you found this useful. You can also comment your queries for fast response. 
Share:

No comments:

Post a Comment

Follow by Email

Popular Posts