Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: TOTALING ON A SEPERATE WORKSHEET

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think the DCOUNT function is exactly what you're looking for.
    It's never too late to learn something new.

    Ricky

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin,


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

    Jim


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •