+ Reply to Thread
Results 1 to 3 of 3

HELP! How to adjust headcount formula in attached document?

  1. #1
    Registered User
    Join Date
    02-25-2014
    Location
    Burnaby, BC
    MS-Off Ver
    Excel 2010
    Posts
    29

    HELP! How to adjust headcount formula in attached document?

    Hello:

    I've posted a document in which I'm trying to calculate the weekly headcount for employees using their timecard entries. There are many entries per week for each employee, but I want to count them only once per week rather than each time they make a timecard entry. Someone on this forum kindly provided the guts for my current formula, but I need to tweak it to deal with one condition that it currently doesn't handle. The person who provided has said he doesn't check in with the forum very often, so I'm putting this out there to a wider audience.

    Whenever the value of column W = "UTO" (unpaid time off) I want the formula to ignore that entry (count it as 0 rather than 1) and continue on to the next entry. The way I have the formula now, it counts any "UTO" entries as 0, but then it does not count the subsequent non-UTO entry as 1. I'm not expert enough to fully understand the formula that was provided --(COUNTIFS(B$2:B945,B945,U$2:U945,U945)=1). I have added embedded the formula in an IF statement to ignore the "UTO" entries.

    =IF([@[Util?]]="UTO",0,--(COUNTIFS(B$2:B945,B945,U$2:U945,U945)=1)) ("Util?" is the header for column W)

    Many thanks in advance for your help!

    Attached Files Attached Files
    Last edited by PWM; 05-28-2014 at 08:14 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: HELP! How to adjust headcount formula in attached document?

    Maybe you just nest in one extra criteria in the COUNTIFS?

    AB2:

    =IF([@[Util?]]="UTO",0,--(COUNTIFS(B$2:B2,B2,U$2:U2,U2,W$2:W2,"<>"&"UTO")=1))

    Animation studio... drool..
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    02-25-2014
    Location
    Burnaby, BC
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: HELP! How to adjust headcount formula in attached document?

    THANK YOU! It appears to do exactly what I need. Much appreciated!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Auto-Sum Cells over multiple worksheets - Real document is attached
    By marshallstrong in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2014, 12:59 PM
  2. Replies: 6
    Last Post: 05-12-2014, 09:16 PM
  3. Replies: 1
    Last Post: 07-17-2013, 11:34 AM
  4. Replies: 0
    Last Post: 05-16-2012, 04:16 PM
  5. adjust row height of secured document
    By jhucks8 in forum Excel General
    Replies: 1
    Last Post: 06-15-2006, 08:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1