Finding number of values in a range on a per year basis

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LyleB_Austin - 20 Sep 2005 20:05 GMT

I have a column of data that represents task durations. I have a separate
column that indicates the year in which the task was completed. I want to
get a count of the number of tasks with a duration of 0-30 days, 31-60 days,
61-90 days etc. for each year represented on the spreadsheet. I know how to
get all tasks within a range, but I don't know how to limit it to just one
year, when there are several different years involved. In other words I want
0-30, 31-60, etc. for 2003, the same for 2004, 2005, etc. In some cases the
duration value is blank. Thanks.

Reply to this Message

Bernard Liengme - 20 Sep 2005 21:22 GMT

A count of tasks with year 2001 and duration 30 or less:
=SUMPRODUCT(--(B2:B20=2001),--(A2:A20<30))
I have assumed year in B and duration in A

Next one: =SUMPRODUCT(--(A2:A22>30),--(A2:A22<60),--(B2:B22=2001))

best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>I have a column of data that represents task durations. I have a separate
> column that indicates the year in which the task was completed. I want to
[quoted text clipped - 8 lines]
> the
> duration value is blank. Thanks.

Reply to this Message

LyleB_Austin - 20 Sep 2005 21:49 GMT

Thanks mucho!

> A count of tasks with year 2001 and duration 30 or less:
> =SUMPRODUCT(--(B2:B20=2001),--(A2:A20<30))
[quoted text clipped - 15 lines]
> > the
> > duration value is blank. Thanks.

Reply to this Message