Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding number of values in a range on a per year basis
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. |
#2
|
|||
|
|||
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:A2230),--(A2:A22<60),--(B2:B22=2001)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "LyleB_Austin" wrote in message ... 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. |
#3
|
|||
|
|||
Thanks mucho!
"Bernard Liengme" wrote: 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:A2230),--(A2:A22<60),--(B2:B22=2001)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "LyleB_Austin" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding the right number | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
Finding LARGE value within range of lookup table | Excel Discussion (Misc queries) | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
How to add one number to a range of numbers | Excel Worksheet Functions |