|
Counting number of weeks to complete a task
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. |
Counting number of weeks to complete a task
Try this:
=SUM(N(FREQUENCY(IF(Task="Painting",MATCH(Week,Wee k,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 " wrote: 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. |
Counting number of weeks to complete a task
On Sun, 13 Apr 2008 05:26:04 -0700 (PDT), wrote:
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. 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 |
Counting number of weeks to complete a task
|
Counting number of weeks to complete a task
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 wrote in message ... 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. |
Counting number of weeks to complete a task
On Apr 14, 3:44*am, "T. Valko" wrote:
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 wrote in message ... 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.- Hide quoted text - - Show quoted text - Thanks everyone! You are lifesavers! Biff's worked the best! |
Counting number of weeks to complete a task
wrote in message
... On Apr 14, 3:44 am, "T. Valko" wrote: 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 wrote in message ... 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.- Hide quoted text - - Show quoted text - Thanks everyone! You are lifesavers! Biff's worked the best! You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
Counting number of weeks to complete a task
Hi,
You may also use the SUMIF() formula as follows: =SUMIF(B1:B9,"="&A11,A1:A9) A11 has either Painting or drawing. -- Regards, Ashish Mathur www.ashishmathur.com "T. Valko" wrote in message ... wrote in message ... On Apr 14, 3:44 am, "T. Valko" wrote: 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 wrote in message ... 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.- Hide quoted text - - Show quoted text - Thanks everyone! You are lifesavers! Biff's worked the best! You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
| All times are GMT +1. The time now is 05:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com