Countifs based on another tables components

[SageTeamZZ]

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

<tbody>
[TD="class: xl63"]Table-1[/TD]
[TD="width: 259"][/TD]

[TD="class: xl66"]Distinct Line No [/TD]
[TD="class: xl66"]Capacity[/TD]

[TD="class: xl65, align: center"]L1[/TD]
[TD="class: xl64"]60 X Count of Working Days (From Table 2)[/TD]

[TD="class: xl65, align: center"]L2[/TD]
[TD="class: xl64"]60 X Count of Working Days (From Table 2)[/TD]

[TD="class: xl65, align: center"]L3[/TD]
[TD="class: xl64"]60 X Count of Working Days (From Table 2)[/TD]

[TD="class: xl65, align: center"]L4[/TD]
[TD="class: xl64"]60 X Count of Working Days (From Table 2)[/TD]

</tbody>

<tbody>
[TD="class: xl65"]Table-2[/TD]
[TD="width: 259"][/TD]
[TD="width: 64"][/TD]

[TD="class: xl67"]Line No 1[/TD]
[TD="class: xl67"]Date[/TD]
[TD="class: xl67"]Status[/TD]

[TD="class: xl66, align: center"]L1[/TD]
[TD="class: xl68, align: center"]Wednesday, July 3, 2019[/TD]
[TD="class: xl69"]Working[/TD]

[TD="class: xl66, align: center"]L1[/TD]
[TD="class: xl68, align: center"]Thursday, July 4, 2019[/TD]
[TD="class: xl69"]Working[/TD]

[TD="class: xl66, align: center"]L2[/TD]
[TD="class: xl68, align: center"]Wednesday, July 3, 2019[/TD]
[TD="class: xl69"]Working[/TD]

[TD="class: xl66, align: center"]L2[/TD]
[TD="class: xl68, align: center"]Thursday, July 4, 2019[/TD]
[TD="class: xl69"]Working[/TD]

[TD="class: xl66, align: center"]L3[/TD]
[TD="class: xl68, align: center"]Wednesday, July 3, 2019[/TD]
[TD="class: xl69"]Working[/TD]

[TD="class: xl66, align: center"]L3[/TD]
[TD="class: xl68, align: center"]Thursday, July 4, 2019[/TD]
[TD="class: xl69"]Holiday[/TD]

[TD="class: xl66, align: center"]L4[/TD]
[TD="class: xl68, align: center"]Wednesday, July 3, 2019[/TD]
[TD="class: xl69"]Working[/TD]

</tbody>

 

[Aladin Akyurek]

What are "some conditions" and what is the expected "count"?

 

[SageTeamZZ]

What are "some conditions" and what is the expected "count"?

Thanks for the concern.

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

 

[Aladin Akyurek]

Thanks for the concern.

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

Book1
	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

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

 

[SageTeamZZ]

	A	B	C
Line No 1	Date	Status
Working
Working
Working
Working
Working
Holiday
Working

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]L1[/TD]
[TD="align: center"]7/3/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]L1[/TD]
[TD="align: center"]7/4/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]L2[/TD]
[TD="align: center"]7/3/2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]L2[/TD]
[TD="align: center"]7/4/2019[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]L3[/TD]
[TD="align: center"]7/3/2019[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]L3[/TD]
[TD="align: center"]7/4/2019[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]L4[/TD]
[TD="align: center"]7/3/2019[/TD]

</tbody>

Sheet2​

	A	B	C
Distinct Line No	Capacity	Count
60 X Count of Working Days (From Table 2)
60 X Count of Working Days (From Table 2)
60 X Count of Working Days (From Table 2)
60 X Count of Working Days (From Table 2)

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]L1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]L2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]L3[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]L4[/TD]

[TD="align: right"]0[/TD]

</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

 

[Aladin Akyurek]

I SALUTE YOU

Thank you my friend

You are welcome.