Countifs based on another tables components
Results 1 to 6 of 6

Thread: Countifs based on another tables components

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Countifs based on another tables components

    I have two tables one of them contains a particular's distinct values as a primary key. and another table consists the same particular's multiple values for different dates along with few more details. now based on some conditions i want to bring the outcome count to the first table for further calculation

    I HOPE ANY HUMBLE PERSON WILL BE HELP ME ON THE FORMULA

    Data Model for both of the tables has been mentioned in the below.

    thanks in advance

    Table-1
    Distinct Line No Capacity
    L1 60 X Count of Working Days (From Table 2)
    L2 60 X Count of Working Days (From Table 2)
    L3 60 X Count of Working Days (From Table 2)
    L4 60 X Count of Working Days (From Table 2)


    Table-2
    Line No 1 Date Status
    L1 Wednesday, July 3, 2019 Working
    L1 Thursday, July 4, 2019 Working
    L2 Wednesday, July 3, 2019 Working
    L2 Thursday, July 4, 2019 Working
    L3 Wednesday, July 3, 2019 Working
    L3 Thursday, July 4, 2019 Holiday
    L4 Wednesday, July 3, 2019 Working

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Countifs based on another tables components

    What are "some conditions" and what is the expected "count"?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs based on another tables components

    Quote Originally Posted by Aladin Akyurek View Post
    What are "some conditions" and what is the expected "count"?
    Thanks for the concern.

    the conditions are status="working" and [Date]>Today()

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Countifs based on another tables components

    Quote Originally Posted by SageTeamZZ View Post
    Thanks for the concern.

    the conditions are status="working" and [Date]>Today()
    ABC
    1Line No 1DateStatus
    2L17/3/2019Working
    3L17/4/2019Working
    4L27/3/2019Working
    5L27/4/2019Working
    6L37/3/2019Working
    7L37/4/2019Holiday
    8L47/3/2019Working

    Sheet2



    ABC
    1Distinct Line NoCapacityCount
    2L160 X Count of Working Days (From Table 2)1
    3L260 X Count of Working Days (From Table 2)1
    4L360 X Count of Working Days (From Table 2)0
    5L460 X Count of Working Days (From Table 2)0

    Sheet1



    In C2 of Sheet2 enter and copy down:

    =COUNTIFS(Sheet2!$A$2:$A$8,$A2,Sheet2!$B$2:$B$8,">"&TODAY(),Sheet2!$C$2:$C$8,"working")
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: Countifs based on another tables components

    Quote Originally Posted by Aladin Akyurek View Post
    A B C
    1 Line No 1 Date Status
    2 L1 7/3/2019 Working
    3 L1 7/4/2019 Working
    4 L2 7/3/2019 Working
    5 L2 7/4/2019 Working
    6 L3 7/3/2019 Working
    7 L3 7/4/2019 Holiday
    8 L4 7/3/2019 Working
    Sheet2

    A B C
    1 Distinct Line No Capacity Count
    2 L1 60 X Count of Working Days (From Table 2) 1
    3 L2 60 X Count of Working Days (From Table 2) 1
    4 L3 60 X Count of Working Days (From Table 2) 0
    5 L4 60 X Count of Working Days (From Table 2) 0
    Sheet1

    In C2 of Sheet2 enter and copy down:

    =COUNTIFS(Sheet2!$A$2:$A$8,$A2,Sheet2!$B$2:$B$8,">"&TODAY(),Sheet2!$C$2:$C$8,"working")

    I SALUTE YOU

    Thank you my friend

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Countifs based on another tables components

    Quote Originally Posted by SageTeamZZ View Post
    I SALUTE YOU

    Thank you my friend
    You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

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
  •