TOTALING ON A SEPERATE WORKSHEET

[jbyrne]

Okay, I have asked this before but can't seem to get it to work. I was wondering if anyone else had input. I have 2 worksheets called "stats" and "data".

On the data worksheet in column B, I have employee initials.
In column D, I have text either "TR" or "TO".
In column E, I have text either "yes" or "no"

On the "stats" worksheet, Column A is the employee initials.
In column B, total number of TO's for each employee.
In column C, total number of TR's for each employee.
In column D, the percent of TR's with yes's for each employee.
In column E, the percent of TO's with yes's for each employee.

How do I do this? Any help would be appreciated.

[Ricky Morris]

I think the DCOUNT function is exactly what you're looking for.

[Aladin Akyurek]

On 2002-04-22 20:34, jbyrne wrote:
Okay, I have asked this before but can't seem to get it to work. I was wondering if anyone else had input. I have 2 worksheets called "stats" and "data".

On the data worksheet in column B, I have employee initials.
In column D, I have text either "TR" or "TO".
In column E, I have text either "yes" or "no"

On the "stats" worksheet, Column A is the employee initials.
In column B, total number of TO's for each employee.
In column C, total number of TR's for each employee.
In column D, the percent of TR's with yes's for each employee.
In column E, the percent of TO's with yes's for each employee.

How do I do this? Any help would be appreciated.

I'll assume that B2:B100 in Data (I suppose this to be the name of your worksheet) houses the employee initials, D2:D100 the TO/TR values, and E2:E100 the yes/no values.

I'll assume that A2:25 in Stats (I suppose this to be the name of your second worksheet) houses a unique list of the employee initials.

In B1 enter: TO
In C1 enter: TR
In D1 enter: TO/yes %
In E1 enter: TR/yes %

Notice that I changed the order for D and E.

In B2 enter:

=SUMPRODUCT((Data!$B$2:$B$100=$A2)*(Data!$D$2:$D$100=B$1))

Copy this first to C2 then copy down.

In D2 enter:

=B2/(MAX(1,SUMPRODUCT((Data!$B$2:$B$100=$A2)*(Data!$D$2:$D$100=B$1)*(Data!$E$2:$E$100="yes")))

Copy this first to E2 and copy down.

Aladin

[jbyrne]

Thanks Aladin,


I'm going to work on this tonight. Thanks for the help!

Jim