March 10, 2010

Counting of Data between two dates

If we have data in a large data base and they are arranged by dates and if we would like to count a particular type of data between two dates. We do that easily, please have a look at the below example.



NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

In the above example, we have attendance details of students. We have counted the number students present between 2/10/2010 and 2/15/2010.

In B10, B11 and B13 We have given the start date, end date and the status of the attendance for which we need the count. The start date is 2/10/2010, end date is 2/14/2010 and the status is Present. We have used =SUM((B2:H2>=B10)*(B2:H2<=B11)*(B3:H8=B12)) formula to get the above stated count. Once you entered this formula, do not hit on the enter butter button if you did so, the formula won’t work, because this is an array formula. We need to create an array for this formula. Creating an array is very simple. After input of the formula, hold down Shift+control and hit on the enter button. The array will be created automatically.

The above formula works in three ways. 1st it would look for the date greater than or equal to 2/10/2010 (Start date) in the range between B2 and H2, 2nd it would look for the date less than or equal to 2/14/2010 (End date) in the same range of cells. 3rd it would count the number of presents available in the range of B3 to H8.

No comments:

Post a Comment