+ Reply to Thread
Results 1 to 13 of 13

Head count for a perticulare date

  1. #1
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Head count for a perticulare date

    Hi All
    I want to calculate the available FT count for any particular date which is reflecting in B column.
    as an Example in D2 I want to calculate how many individuals were available on 5/27/2019.
    Answer is 4
    satish.manjunath is reflecting 2 times but it should calculate as an individual so the number should
    reflect in D2 is 4.

    Need it urgently
    Regards
    Arindam

    Help.xlsx

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    Re: Head count for a perticulare date

    Maybe this:

    =SUM(IF(FREQUENCY(IF(date_range="5/27/2019"),name_range)>0,1))

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Re: Head count for a perticulare date

    Hi,Attachment 626338

    Thanks for your response.
    but when I put the formula it is showing some error. I have attached the file..
    please try the formula in the above-attached file.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    Re: Head count for a perticulare date

    Sorry - ignore that. I will think again.

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Head count for a perticulare date

    Please try at D2

    =SUMPRODUCT((1/COUNTIF(INDEX(C:C,MATCH(B2,$B$1:$B$2155,)):INDEX(C:C,MATCH(2,INDEX(1/($B$1:$B$2155=B2),))),INDEX(C:C,MATCH(B2,$B$1:$B$2155,)):INDEX(C:C,MATCH(2,INDEX(1/($B$1:$B$2155=B2),))))))

  6. #6
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Re: Head count for a perticulare date

    Hi, When I am applying your formula in my original sheet it is giving a different result for the same date.
    as an example in this current attached file when I am selecting the date as 3/1/2019 there are 24 names are reflecting and when I am removing the duplicated it is 14 so the actual results should come oft 14 for all the lines where the date is 3/1/2019 but it is reflecting the different result in different coloum.

    please help me on getting the correct formula.

    Attachment 626492

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    Re: Head count for a perticulare date

    Administrative Note:

    We don't expect you to PM members to prompt them to look at your issues. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

  8. #8
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Re: Head count for a perticulare date

    I have not pressurized you at all. as I saw you online so just ask you that is you got any result on the same or not.
    Sorry for the inconvenience..

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    Re: Head count for a perticulare date

    Part of the problem is that you have mangled the formula. This is what you should have used:

    =SUMPRODUCT((1/COUNTIF(INDEX(BJ:BJ,MATCH(BW2,$BW$1:$BW$2155,)):INDEX(BJ:BJ,MATCH(2,INDEX(1/($BW$1:$BW$2155=BW2),))),INDEX(BJ:BJ,MATCH(BW2,$BW$1:$BW$2155,)):INDEX(BJ:BJ,MATCH(2,INDEX(1/($BW$1:$BW$2155=BW2),))))))

    You will have to wait for Bo_Ry to sort out the unique count aspect, but this amendment will sort out the multiple results.

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Head count for a perticulare date

    Quote Originally Posted by arindamsenaxa View Post
    Hi,Attachment 626338

    Thanks for your response.
    but when I put the formula it is showing some error. I have attached the file..
    please try the formula in the above-attached file.

    I can't download, it show "Invalid Attachment specified"

  11. #11
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Re: Head count for a perticulare date

    Attachment 626514
    please check this file for referance

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Head count for a perticulare date

    Please try array entering (Ctrl + Shift + Enter) this in your last upload.

    (I had to clear the filters in order to see if it worked.)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  13. #13
    Registered User
    Join Date
    11-28-2014
    Location
    india
    MS-Off Ver
    7
    Posts
    141

    Re: Head count for a perticulare date

    Yes N,Now this is working...Thanks a lot

+ 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. Head Count with countif condition
    By Vcare in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-04-2018, 03:10 AM
  2. Head count by department by date
    By SYOPS in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-06-2016, 06:39 PM
  3. [SOLVED] SUMIF for head count
    By jackson_hollon in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-16-2014, 04:46 AM
  4. [SOLVED] Invoice count per head per month extract
    By makinmomb in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-09-2014, 11:21 AM
  5. Formula for Changes in Head Count
    By bbarnett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-30-2012, 12:41 PM
  6. Head Count by criteria
    By eshen in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 05-12-2010, 11:40 AM
  7. head count on rota
    By nick in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2005, 04:06 PM

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