Countifs based on another tables components

SageTeamZZ

New Member
Joined
Jul 3, 2019
Messages
3
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
L160 X Count of Working Days (From Table 2)
L260 X Count of Working Days (From Table 2)
L360 X Count of Working Days (From Table 2)
L460 X Count of Working Days (From Table 2)

<tbody>
</tbody>


Table-2
Line No 1DateStatus
L1Wednesday, July 3, 2019Working
L1Thursday, July 4, 2019Working
L2Wednesday, July 3, 2019Working
L2Thursday, July 4, 2019Working
L3Wednesday, July 3, 2019Working
L3Thursday, July 4, 2019Holiday
L4Wednesday, July 3, 2019Working

<tbody>
</tbody>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Thanks for the concern.

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


Book1
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



Book1
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")
 
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top