Counting number of weeks to complete a task

  • Thread starter Thread starter roniaelm
  • Start date Start date
R

roniaelm

Hi,

Can anyone help me please.
I have a spreadsheet and I am trying to calculate how many weeks a
task took to complete.
The task does not necessairly have to be completed in consecutive
order.

A brief example:

Week Task
1 Painting
1 Painting
1 Drawing
2 Drawing
2 Drawing
2 Drawing
3 Painting
3 Drawing
3 Drawing

From the table the Painting task was completed in 2 weeks and the
Drawing task was completed in 3 weeks.

I would appreciate your help.
 
Try this:

=SUM(N(FREQUENCY(IF(Task="Painting",MATCH(Week,Week,0)),MATCH(Week,Week,0))>0))

=SUM(N(FREQUENCY(IF(Task="Drawing",MATCH(Week,Week,0)),MATCH(Week,Week,0))>0))

Both formulas are required with CRTL+SHIFT+ENTER, not just ENTER
 
Hi,

Can anyone help me please.
I have a spreadsheet and I am trying to calculate how many weeks a
task took to complete.
The task does not necessairly have to be completed in consecutive
order.

A brief example:

Week Task
1 Painting
1 Painting
1 Drawing
2 Drawing
2 Drawing
2 Drawing
3 Painting
3 Drawing
3 Drawing

From the table the Painting task was completed in 2 weeks and the
Drawing task was completed in 3 weeks.

I would appreciate your help.
Click to expand...

One way:

You could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english

Then use this **array** formula:

=COUNT(UNIQUEVALUES(IF(Task="painting",Week)))

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
XL will place braces {...} around the formula.

Task & Week are the named ranges for your data.
--ron
 
Assume:

A2:A10 = Week
B2:B10 = Task

D2:D3 = Painting; Drawing

If there are no empty cells within Week, try this arry formula** :

=COUNT(1/FREQUENCY(IF(Task=D2,Week),Week))

If there might be empty cells within Week, use this version** :

=COUNT(1/FREQUENCY(IF((Task=D2)*(Week<>""),Week),Week))

Copy down to D3.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Assume:

A2:A10 = Week
B2:B10 = Task

D2:D3 = Painting; Drawing

If there are no empty cells within Week, try this arry formula** :

=COUNT(1/FREQUENCY(IF(Task=D2,Week),Week))

If there might be empty cells within Week, use this version** :

=COUNT(1/FREQUENCY(IF((Task=D2)*(Week<>""),Week),Week))

Copy down to D3.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP











- Show quoted text -
Click to expand...

Thanks everyone!
You are lifesavers!
Biff's worked the best!
 
Assume:

A2:A10 = Week
B2:B10 = Task

D2:D3 = Painting; Drawing

If there are no empty cells within Week, try this arry formula** :

=COUNT(1/FREQUENCY(IF(Task=D2,Week),Week))

If there might be empty cells within Week, use this version** :

=COUNT(1/FREQUENCY(IF((Task=D2)*(Week<>""),Week),Week))

Copy down to D3.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP











- Show quoted text -
Thanks everyone!
You are lifesavers!
Biff's worked the best!
Click to expand...

You're welcome. Thanks for the feedback!
 
Hi,

You may also use the SUMIF() formula as follows:

=SUMIF(B1:B9,"="&A11,A1:A9)

A11 has either Painting or drawing.
 
Back
Top