Counting or summing up a group of cells with dates as datas

[Irene]

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.

 

[Peo Sjoblom]

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

 

[ShaneDevenshire]

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((D>G5)*(D<G6))
and so on

 

[Irene]

Thank you all!!! it helped and it worked!!!

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