Tracking activities
 

Hi,
I have an excel table with following fields and sample data

DOCNAME NUMPAGES PERSON %COMPLETE
Doc1 20 A 50
Doc2 52 B 30
Doc3 26 D 60
Doc4 60 A/C/D 80
Doc5 15 B/C 100
...etc..

I need to make a summary sheet containing how much each person has
completed, like:

PERSON %COMPLETED
A ?? <-- X
B ??
C ??
D ??

The way to compute X would be:
Sum (NUMPAGES * %COMPLETED * Factor) for all rows where A is the
person. Factor is calculated as:
1, if 1 person is doing it
1/2, if 2 person is doing it
1/3, if 3 person is doing it

I know about implementing multiple conditions in SUMPRODUCT, but
unable to understand how to implement this.

Any clues would be appreciated!

Regards,
~Animesh

Hi
try the following:
1. Create a helper column with the following formula to calculate the number
of involved persons:
=LEN(C1)-LEN(SUBSTITUTE(A1,"/",""))+1
and copy this for all rows.
Lets assume this is in column E then use the following formula
=SUMPRODUCT(--(ISNUMBER(FIND("A",C1:C100)),B1:B100,C1:C100,(1/E1:E100))

"Animesh" wrote:

Hi,
I have an excel table with following fields and sample data

DOCNAME NUMPAGES PERSON %COMPLETE
Doc1 20 A 50
Doc2 52 B 30
Doc3 26 D 60
Doc4 60 A/C/D 80
Doc5 15 B/C 100
...etc..

I need to make a summary sheet containing how much each person has
completed, like:

PERSON %COMPLETED
A ?? <-- X
B ??
C ??
D ??

The way to compute X would be:
Sum (NUMPAGES * %COMPLETED * Factor) for all rows where A is the
person. Factor is calculated as:
1, if 1 person is doing it
1/2, if 2 person is doing it
1/3, if 3 person is doing it

I know about implementing multiple conditions in SUMPRODUCT, but
unable to understand how to implement this.

Any clues would be appreciated!

Regards,
~Animesh