TOTALING ON A SEPERATE WORKSHEET

jbyrne

Board Regular
Joined
Feb 22, 2002
Messages
178
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top