Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula excel 2003 (2003)

    I want to get advice from you with regard to formulas in excel.e.g. If I want to calculate the number of employees who have taken leaves in this financial year but in excel one employee has repeated many times depending on leave taken, I want excel to calculate one employee irrespective of leaves taken, please help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula excel 2003 (2003)

    Could you provide a little more details on what you are after. Perhaps attach a representative sample workbook with test data?

    Steve

  3. #3
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula excel 2003 (2003)

    The example is N. Peters took sick leave more than once so the data indicates that name many times according to sick leave taken. I want Peters to be indicated once not according to number of leave taken. ( Sick leave was taken in Jan, June, Sept, Dec etc = it must indicate 1 person). I have attached a sample.

    Regards
    Sharon
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula excel 2003 (2003)

    How about something like this Array (confirm with ctrl-shift-enter).
    =SUM(1/COUNTIF(A2:A100,A2:A100&""))-(COUNTBLANK(A2:A100)>0)

    It counts the unique entries, adjust the range as needed

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Formula excel 2003 (2003)

    You could create a pivot table that would give you an array of each unique name in the table and the number of rows that exists for each person.

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula excel 2003 (2003)

    Or, you could populate Col M with a formula to detect duplicates and custom filter on "Not Equal" or Blanks as shown in the attached file.
    Attached Files Attached Files
    Regards
    Don

  7. #7
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula excel 2003 (2003)

    Thanks alot, pivot table gave me exactly what i wanted. U're a STAR!!

  8. #8
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula excel 2003 (2003)

    Thanks alot Steve, the formula worked.

Posting Permissions

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