Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting or summing up a group of cells with dates as datas
Hi,
I am making a report of numbers of clients we have served by quarter. Example, in column A, I have dates as to when the clients entered in or enrolled to our program starting from July of the current year to June of next year. Our first quarter is from July to September, 2nd is October to December, and so on. I have a column in another worksheet for 1st qtr, 2nd qtr, 3rd qtr, and 4rth qtr. What formula should I have in my 1st qtr column, 2nd qtr column, 3rd qtr column and 4th qtr column so I could get the number of clients entered or enrolled? Any help would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting or summing up a group of cells with dates as datas
I would use autofilter and filter custom for dates "is greater than or equal
to" and put in 07/01/08 then AND "is less than or equal to" and put in 09/30/08 Then use =SUBTOTAL(3,A2:A500) somewhere above the first row (replace A2:A500 with your actual data) to get clients for the first quarter Another way =SUMPRODUCT(--(A2:A500=--"2008-07-01"),--(A2:A500<=--"2008-09-30")) will count all dates for the first quarter of this year, easiest would be to use 2 extra cells where you would put first date and last date of the quarter =SUMPRODUCT(--(A2:A500=H2),--(A2:A500<=I2)) where you only need to change those dates and not edit the formula -- Regards, Peo Sjoblom "Irene" wrote in message ... Hi, I am making a report of numbers of clients we have served by quarter. Example, in column A, I have dates as to when the clients entered in or enrolled to our program starting from July of the current year to June of next year. Our first quarter is from July to September, 2nd is October to December, and so on. I have a column in another worksheet for 1st qtr, 2nd qtr, 3rd qtr, and 4rth qtr. What formula should I have in my 1st qtr column, 2nd qtr column, 3rd qtr column and 4th qtr column so I could get the number of clients entered or enrolled? Any help would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting or summing up a group of cells with dates as datas
Hi,
Suppose you enter a little table like this in 7/1/2008 10/1/2008 1/1/2009 4/1/2009 9/30/2008 12/30/2008 3/30/2009 6/30/2009 in the range G5:J6 Suppose your date column runs from A1:A99 with a title in A1. Then you could use =SUMPRODUCT((A1:A99=G5)*(A2:A99<=G6)) or if you name the range with the dates D this formula would be =SUMPRODUCT((D=G5)*(D<=G6)) For 2nd qtr: =SUMPRODUCT((D=H5)*(D<=H6)) For 3rd qtr: =SUMPRODUCT((D=I5)*(D<=I6)) For 4th qtr: =SUMPRODUCT((D=J5)*(D<=J6)) You can make this look simplier by changing the dates in above range to read: 6/30/2008 9/30/2008 12/30/2008 3/30/2009 10/1/2008 1/1/2009 4/1/2009 7/1/2009 and then change the formulas as follows: =SUMPRODUCT((DG5)*(D<G6)) and so on -- Thanks, Shane Devenshire "Irene" wrote: Hi, I am making a report of numbers of clients we have served by quarter. Example, in column A, I have dates as to when the clients entered in or enrolled to our program starting from July of the current year to June of next year. Our first quarter is from July to September, 2nd is October to December, and so on. I have a column in another worksheet for 1st qtr, 2nd qtr, 3rd qtr, and 4rth qtr. What formula should I have in my 1st qtr column, 2nd qtr column, 3rd qtr column and 4th qtr column so I could get the number of clients entered or enrolled? Any help would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting or summing up a group of cells with dates as datas
Thank you all!!! it helped and it worked!!!
"Peo Sjoblom" wrote: I would use autofilter and filter custom for dates "is greater than or equal to" and put in 07/01/08 then AND "is less than or equal to" and put in 09/30/08 Then use =SUBTOTAL(3,A2:A500) somewhere above the first row (replace A2:A500 with your actual data) to get clients for the first quarter Another way =SUMPRODUCT(--(A2:A500=--"2008-07-01"),--(A2:A500<=--"2008-09-30")) will count all dates for the first quarter of this year, easiest would be to use 2 extra cells where you would put first date and last date of the quarter =SUMPRODUCT(--(A2:A500=H2),--(A2:A500<=I2)) where you only need to change those dates and not edit the formula -- Regards, Peo Sjoblom "Irene" wrote in message ... Hi, I am making a report of numbers of clients we have served by quarter. Example, in column A, I have dates as to when the clients entered in or enrolled to our program starting from July of the current year to June of next year. Our first quarter is from July to September, 2nd is October to December, and so on. I have a column in another worksheet for 1st qtr, 2nd qtr, 3rd qtr, and 4rth qtr. What formula should I have in my 1st qtr column, 2nd qtr column, 3rd qtr column and 4th qtr column so I could get the number of clients entered or enrolled? Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting dates in cells | Excel Worksheet Functions | |||
Summing cells containing dates | Excel Worksheet Functions | |||
counting cells containing certain ranges of dates | Excel Worksheet Functions | |||
Summing (or counting) cells until there is a blank cell | Excel Discussion (Misc queries) | |||
Counting the number cells between two dates | Excel Discussion (Misc queries) |