+ Reply to Thread
Results 1 to 13 of 13

Counting Value Based on Date in another Sheet

  1. #1
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Counting Value Based on Date in another Sheet

    Dear Experts

    I have just made a simple attendance Sheet.

    Attached sheet has a tab named Holidays and Attendance.
    In the attendance, based on date and Month in Holidays I am updating Column AH.
    Example, In Jan 2021, there are two days in Holidays. That is 14 and 26. In Attendance tab, if there is value "P" on 14 and 26 I am updating AH with Value 2
    Is there anyway to automate this
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Counting Value Based on Date in another Sheet

    You can use:

    =COUNTIFS(Holidays!A:A,">="&$B$1,Holidays!A:A,"<="&EOMONTH($B$1,0))

    Note that B1 is ciurrently set to 1/2/21.... and the formula will retun 0.
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Counting Value Based on Date in another Sheet

    The formula in AH is faulty, too. I suggest you use

    =SUMPRODUCT(($C$3:$AG$3<>"")*(C4:AG4="P"))

    to avoid th epossibility of counting P cvalues that do not exist in (for example) February, a shorter month.
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,990

    Re: Counting Value Based on Date in another Sheet

    First, try to convert row 3 in to date, format as "d"
    C3=B1
    D3=IF(OR(C3="",C3>=EOMONTH($B$1,0)),"",C3+1)
    Drag accross
    AH4 count "P" fall into holiday:

    Please Login or Register  to view this content.
    =2
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Counting Value Based on Date in another Sheet

    I changed to Apr 21.
    C4:C30 all values blank. It still returns 1 in AI

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,548

    Re: Counting Value Based on Date in another Sheet

    I would suggest that you change what look like days of the month on row 3 to actual dates, formatted to show days. To do that, use these formulae in the cells stated:

    C3: =B1

    D3: =C3+1

    Copy this across to AD3, as every month has at least 28 days.

    AE3: =IF(AD3="","",IF(MONTH(AD3+1)<>MONTH(AD3),"",AD3+1))

    Copy this across to AG3, and then apply a Custom Format of d to the cells C3:AG3

    Then in AI4 you can use this array* formula:

    =SUM(COUNTIFS(C$3:AG$3,Holidays!$A$2:$A$11,C4:AG4,"P"))

    which can then be copied down as required.

    *Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual Enter.

    The attached file shows this in operation.

    Hope this helps.

    Pete
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Counting Value Based on Date in another Sheet

    Quote Originally Posted by Glenn Kennedy View Post
    The formula in AH is faulty, too. I suggest you use

    =SUMPRODUCT(($C$3:$AG$3<>"")*(C4:AG4="P"))

    to avoid th epossibility of counting P cvalues that do not exist in (for example) February, a shorter month.
    I changed to Apr 21.
    C4:C30 all values blank. It still returns 1 in AI

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Counting Value Based on Date in another Sheet

    That would be because there is a holiday in April!!!!

  9. #9
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Counting Value Based on Date in another Sheet

    Quote Originally Posted by bebo021999 View Post
    First, try to convert row 3 in to date, format as "d"
    C3=B1
    D3=IF(OR(C3="",C3>=EOMONTH($B$1,0)),"",C3+1)
    Drag accross
    AH4 count "P" fall into holiday:

    Please Login or Register  to view this content.
    =2
    Changed the month to Apr 21. Though there is only one date in holiday list, AI returns 2

  10. #10
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Counting Value Based on Date in another Sheet

    Quote Originally Posted by Glenn Kennedy View Post
    That would be because there is a holiday in April!!!!
    Date 13 I did not enter value. Date 13 is holiday since there is no value P in date 13 it should not return value

  11. #11
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Counting Value Based on Date in another Sheet

    Quote Originally Posted by Glenn Kennedy View Post
    The formula in AH is faulty, too. I suggest you use

    =SUMPRODUCT(($C$3:$AG$3<>"")*(C4:AG4="P"))

    to avoid th epossibility of counting P cvalues that do not exist in (for example) February, a shorter month.
    If there is P in 13 only then it should count
    Attached Files Attached Files

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,990

    Re: Counting Value Based on Date in another Sheet

    Quote Originally Posted by grcshekar View Post
    Changed the month to Apr 21. Though there is only one date in holiday list, AI returns 2
    That formula is for AI, if AI is holiday count.
    In AH, count "P" regardless holiday:
    =COUNTIFS($C$3:$AG$3,"<>",$C4:$AG4,"P")
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Counting Value Based on Date in another Sheet

    Quote Originally Posted by bebo021999 View Post
    That formula is for AI, if AI is holiday count.
    In AH, count "P" regardless holiday:
    =COUNTIFS($C$3:$AG$3,"<>",$C4:$AG4,"P")
    Thank You Very Much

+ 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. Counting Nights Based on a Date Range
    By dfish34 in forum Excel General
    Replies: 4
    Last Post: 03-13-2018, 03:22 PM
  2. Counting data based on date
    By Skeeterj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-04-2014, 11:52 AM
  3. Formula to stop the counting based on date
    By joseph_yap22 in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 08-20-2013, 07:58 AM
  4. Counting based on date ranges
    By smohyee in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-09-2013, 09:56 AM
  5. [SOLVED] Counting based on DATE values
    By Laur3l in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-05-2012, 04:07 PM
  6. Counting Based On 1 Date
    By xolite in forum Excel General
    Replies: 0
    Last Post: 04-11-2011, 04:17 PM
  7. Counting Based on Date
    By hanjimono in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2011, 11:30 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