I need to count all instances of dates within 6 days of a fixed date. Trouble is, I need to do all this within one cell and without creating another table column. I have attempted to use a days360 function inside of a countif function to do this, but that only works for one cell at a time. How do I count all instances of those dates using a single cell and without creating another table column?
Can you post a sample workbook showing expected results.
Try using COUNTIFS?
Assume your range of dates is in column A, and your fixed date is in cell B1, then you could do something like this:
=COUNTIFS(A:A,">="&B1,A:A,"<="&B1+6)
That will return the count of all cells with dates that are equal to, or within 6 days following the date in cell B1.
- Moo
=COUNTIFS(A:A,">="&B1,A:A,"<="&B1+6) worked perfectly
What is the function of the ampersand in this case? Why does it only work as "<="&B1,A:A?
You need the ampersand to concatenate the comparison operators ( < > = ) with the cell reference ( B1 ). This is because Excel treats the comparison operators as text strings. So, you are concatenating "<=" and the contents of cell B1 for the criteria.
If you wanted to compare a value, you could use: =COUNTIFS(A:A,"<50"...), or if you wanted to match cells that were exactly 73, you could just use =COUNTIFS(A:A,73...)
Hope that explanation helps. =)
- Moo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks