How can I sum the unique values (project ID's) in a list (project time log?)

Background:
A sheet in the workbook contains the Project Time Log with the columns:
Project ID, Date, Work Days (ex: 0.25 would equal 1/4 of a day.) A single
entry would be something like: SalesReportAugust, 9/22/06, 0.5. The
'SalesReportAugust' project would be listed multiple times with the dates it
was worked on and how much time it required, in fraction of a day.

What I need:
On another sheet (Project Workload Summary) I would like the unique Project
ID's to list with the total of days worked on. The tricky part is the list
of unique Project ID's. I know it can be done with a query or pivot table or
probably VBA but I would like a solution that does not require one to perform
any 'action' to update the list of unique Project ID's. Ideally, I would
prefer that every time another new Project ID is entered on the Project Time
Log sheet, it would automatically appear on the Project Workload Summary
sheet with its total thus far.

Is there a fancy formula that can accomplish this, maybe with a dynamic
range name mixed in?

A1: ='Project Time Log'!A1
A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,'Project Time
Log'!$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK('Project Time Log'!$A$1:$A$20),"",'Project Time
Log'!$A$1:$A$20),MATCH(0,COUNTIF(A$1:A1,'Project Time
Log'!$A$1:$A$20&""),0)))

A2 is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy A2 down

B1: =IF(A1="","",SUMIF('Project Time Log'!A:A,Sheet3!A1,'Project Time
Log'!C:C))

and copy down

Just make sure you copy down far enough to cope with additions.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bill_S" wrote in message
...
How can I sum the unique values (project ID's) in a list (project time
log?)

Background:
A sheet in the workbook contains the Project Time Log with the columns:
Project ID, Date, Work Days (ex: 0.25 would equal 1/4 of a day.) A single
entry would be something like: SalesReportAugust, 9/22/06, 0.5. The
'SalesReportAugust' project would be listed multiple times with the dates
it
was worked on and how much time it required, in fraction of a day.

What I need:
On another sheet (Project Workload Summary) I would like the unique
Project
ID's to list with the total of days worked on. The tricky part is the
list
of unique Project ID's. I know it can be done with a query or pivot table
or
probably VBA but I would like a solution that does not require one to
perform
any 'action' to update the list of unique Project ID's. Ideally, I would
prefer that every time another new Project ID is entered on the Project
Time
Log sheet, it would automatically appear on the Project Workload Summary
sheet with its total thus far.

Is there a fancy formula that can accomplish this, maybe with a dynamic
range name mixed in?

Assuming that Sheet1, Column A, starting at A2, contains the Project ID,
try...

Sheet2!A2:

=SUM(IF(Sheet1!A2:A100<"",1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))

....confirmed with CONTROL+SHIFT+ENTER.

Sheet2!B2, copied down:

=IF(ROWS(B$2:B2)<=$A$2,INDEX(Sheet1!A2:A$100,MATCH (TRUE,ISNA(MATCH(Sheet1
!A2:A$100,B$1:B1,0)),0)),"")

....confirmed with CONTROL+SHIFT+ENTER.

Now, all you have to do is replace...

Sheet1!A2:A$100

....with the name defining the dynamic range for Column A on Sheet1.

Hope this helps!

In article ,
Bill_S wrote:

How can I sum the unique values (project ID's) in a list (project time log?)

Background:
A sheet in the workbook contains the Project Time Log with the columns:
Project ID, Date, Work Days (ex: 0.25 would equal 1/4 of a day.) A single
entry would be something like: SalesReportAugust, 9/22/06, 0.5. The
'SalesReportAugust' project would be listed multiple times with the dates it
was worked on and how much time it required, in fraction of a day.

What I need:
On another sheet (Project Workload Summary) I would like the unique Project
ID's to list with the total of days worked on. The tricky part is the list
of unique Project ID's. I know it can be done with a query or pivot table or
probably VBA but I would like a solution that does not require one to perform
any 'action' to update the list of unique Project ID's. Ideally, I would
prefer that every time another new Project ID is entered on the Project Time
Log sheet, it would automatically appear on the Project Workload Summary
sheet with its total thus far.

Is there a fancy formula that can accomplish this, maybe with a dynamic
range name mixed in?