| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
How do I count occurences in a date range?
I have a table of data of closed accounts. In that table, I have a column
including the cancellation date. I want to count the number of cases that cancelled in Jan, Feb, etc. I've tried combinations of "countif", "and" and <=, = in all my formulas but I keep getting 0 as my result. |
|
#2
|
|||
|
|||
|
One way:
=SUMPRODUCT(--(MONTH(A1:A50)=1)) Where the *number* of the month (Jan=1, Feb=2, ...etc.) is in the formula OR =SUMPRODUCT(--(MONTH(A1:A50)=C1)) Where the number of the month is entered into another cell, so that it can be easily changed to count other months. If your list is multi-year, you may want to add a specific year to further classify the count: =SUMPRODUCT((MONTH(A1:A50)=1)*(YEAR(A1:A50)=2005)) OR =SUMPRODUCT((MONTH(A1:A50)=C1)*(YEAR(A1:A50)=C2)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jeremy" wrote in message ... I have a table of data of closed accounts. In that table, I have a column including the cancellation date. I want to count the number of cases that cancelled in Jan, Feb, etc. I've tried combinations of "countif", "and" and <=, = in all my formulas but I keep getting 0 as my result. |
|
#3
|
|||
|
|||
|
That first one can give misleading results if there are empty cells in the
range. =SUMPRODUCT(--(MONTH(A1:A50)=1),--(A1:A500)) Is one way around it. RagDyeR wrote: One way: =SUMPRODUCT(--(MONTH(A1:A50)=1)) Where the *number* of the month (Jan=1, Feb=2, ...etc.) is in the formula OR =SUMPRODUCT(--(MONTH(A1:A50)=C1)) Where the number of the month is entered into another cell, so that it can be easily changed to count other months. If your list is multi-year, you may want to add a specific year to further classify the count: =SUMPRODUCT((MONTH(A1:A50)=1)*(YEAR(A1:A50)=2005)) OR =SUMPRODUCT((MONTH(A1:A50)=C1)*(YEAR(A1:A50)=C2)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jeremy" wrote in message ... I have a table of data of closed accounts. In that table, I have a column including the cancellation date. I want to count the number of cases that cancelled in Jan, Feb, etc. I've tried combinations of "countif", "and" and <=, = in all my formulas but I keep getting 0 as my result. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to count number of days in range which are less than today | Excel Worksheet Functions | |||
| Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
| count weekdays in a date range | Excel Worksheet Functions | |||
| How can I sum information in a list with a date range? | Excel Worksheet Functions | |||
| Excel: How to return count for each cell within date range criter. | Excel Worksheet Functions | |||
Hybrid Mode
