Count the number of times a subject appears in a list created by the use of FILTER function

[William53]

HI All
I have created a list of rows of data when using the FILTER function to list from a table any entries that require further action.
In the further action column in the table the answer is yes or no.
I used the FILTER function to get a list of records where the further action column said "Yes"
Example here:

27-Jun-23​	Tue	Jun	23	Housekeeping	Unsafe Condition	Yes	Some areas required attention, Bailey Bridge area, under Piperack 1 at grade, 1st lift scaffold South of stack duct ISBL.	Open
27-Jun-23​	Tue	Jun	23	Housekeeping	Unsafe Condition	Yes	Water bottles all over the site - picked up and disposed off in the bags and skips	Closed
28-Jun-23​	Wed	Jun	23	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
28-Jun-23​	Wed	Jun	23	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
28-Jun-23​	Wed	Jun	23	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
30-Jun-23​	Fri	Jun	23	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
01-Jul-23​	Sat	Jul	23	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
01-Jul-23​	Sat	Jul	23	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open

The above is the result from the FILTER function "=FILTER(AllObs1,AllObs1[Further Action]="Yes") I have tried to place this in a table but will not allow.
This formula expands each time there is a Yes placed in the main table (AllObs1) so no issue with the function working. I have done a countif formula to count the number of subjects in the list such as Housekeeping , Working at height etc but I cant seem to work out how to do it dynamically, e.g., as the list expands the count also expands, I have to go back and expand the range manually to include the new entries.
Any help will be greatly appreciated.
Many thanks
William

 

[RudRud]

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9		16	Housekeeping	1Condition
2	27-Jun-23	Tue	Jun	23	Housekeeping	Unsafe Condition	Yes	Some areas required attention, Bailey Bridge area, under Piperack 1 at grade, 1st lift scaffold South of stack duct ISBL.	Open		7	Work at Height	2Condition Work at Height with Yes
3	27-Jun-23	Tue	Jun	24	Housekeeping	Unsafe Condition	Yes	Water bottles all over the site - picked up and disposed off in the bags and skips	Closed		18		Housekeeping or work at height ( height that Closed)
4	28-Jun-23	Wed	Jun	25	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
5	28-Jun-23	Wed	Jun	26	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed				Or +
6	28-Jun-23	Wed	Jun	27	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed				And *
7	30-Jun-23	Fri	Jun	28	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
8	01-Jul-23	Sat	Jul	29	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
9	01-Jul-23	Sat	Jul	30	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
10	28-Jun-23	Wed	Jun	31	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
11	28-Jun-23	Wed	Jun	32	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
12	28-Jun-23	Wed	Jun	33	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
13	30-Jun-23	Fri	Jun	34	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
14	01-Jul-23	Sat	Jul	35	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
15	01-Jul-23	Sat	Jul	36	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
16	27-Jun-23	Tue	Jun	37	Housekeeping	Unsafe Condition	NO	Some areas required attention, Bailey Bridge area, under Piperack 1 at grade, 1st lift scaffold South of stack duct ISBL.	Open
17	27-Jun-23	Tue	Jun	38	Housekeeping	Unsafe Condition	NO	Water bottles all over the site - picked up and disposed off in the bags and skips	Closed
18	28-Jun-23	Wed	Jun	39	Work at Height	Unsafe Condition	NO	Loose materials on scaffolds at height - removed in the shift	Closed
19	28-Jun-23	Wed	Jun	40	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
20	28-Jun-23	Wed	Jun	41	Environmental Health	Unsafe Condition	No	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
21	30-Jun-23	Fri	Jun	42	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
22	01-Jul-23	Sat	Jul	43	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
23	01-Jul-23	Sat	Jul	44	Work at Height	Unsafe Condition	No	Housekeeping Required	Open
24	28-Jun-23	Wed	Jun	45	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
25	28-Jun-23	Wed	Jun	46	Housekeeping	Unsafe Condition	No	Needed at height on scaffolds - undertaken during the night	Closed
26	28-Jun-23	Wed	Jun	47	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
27	30-Jun-23	Fri	Jun	48	Housekeeping	Unsafe Condition	No	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
28	01-Jul-23	Sat	Jul	49	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
29	01-Jul-23	Sat	Jul	50	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
30	01-Jul-23	Sat	Jul	50	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
Sheet1

Cell Formulas
Range	Formula
K1	K1	=COUNT(FILTER(AllObs1,(AllObs1[Column5]="Housekeeping")))
K2	K2	=COUNT(FILTER(AllObs1,(AllObs1[Column5]=L2)*(AllObs1[Column7]="Yes")))
K3	K3	=COUNT(FILTER(AllObs1,((AllObs1[Column5]="Work at Height")+(AllObs1[Column5]="Housekeeping"))*(AllObs1[Column9]="Closed")))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$30:$I$30	K1:K3

Is it what you're looking into? CMIIW

FYR

 

[Fluff]

How about

=LET(u,UNIQUE(INDEX(A2#,,5)),HSTACK(u,COUNTIFS(INDEX(A2#,,5),u)))

Change the A2# to the cell with the filter function.

 

[William53]

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9		16	Housekeeping	1Condition
2	27-Jun-23	Tue	Jun	23	Housekeeping	Unsafe Condition	Yes	Some areas required attention, Bailey Bridge area, under Piperack 1 at grade, 1st lift scaffold South of stack duct ISBL.	Open		7	Work at Height	2Condition Work at Height with Yes
3	27-Jun-23	Tue	Jun	24	Housekeeping	Unsafe Condition	Yes	Water bottles all over the site - picked up and disposed off in the bags and skips	Closed		18		Housekeeping or work at height ( height that Closed)
4	28-Jun-23	Wed	Jun	25	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
5	28-Jun-23	Wed	Jun	26	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed				Or +
6	28-Jun-23	Wed	Jun	27	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed				And *
7	30-Jun-23	Fri	Jun	28	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
8	01-Jul-23	Sat	Jul	29	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
9	01-Jul-23	Sat	Jul	30	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
10	28-Jun-23	Wed	Jun	31	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
11	28-Jun-23	Wed	Jun	32	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
12	28-Jun-23	Wed	Jun	33	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
13	30-Jun-23	Fri	Jun	34	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
14	01-Jul-23	Sat	Jul	35	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
15	01-Jul-23	Sat	Jul	36	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
16	27-Jun-23	Tue	Jun	37	Housekeeping	Unsafe Condition	NO	Some areas required attention, Bailey Bridge area, under Piperack 1 at grade, 1st lift scaffold South of stack duct ISBL.	Open
17	27-Jun-23	Tue	Jun	38	Housekeeping	Unsafe Condition	NO	Water bottles all over the site - picked up and disposed off in the bags and skips	Closed
18	28-Jun-23	Wed	Jun	39	Work at Height	Unsafe Condition	NO	Loose materials on scaffolds at height - removed in the shift	Closed
19	28-Jun-23	Wed	Jun	40	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
20	28-Jun-23	Wed	Jun	41	Environmental Health	Unsafe Condition	No	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
21	30-Jun-23	Fri	Jun	42	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
22	01-Jul-23	Sat	Jul	43	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
23	01-Jul-23	Sat	Jul	44	Work at Height	Unsafe Condition	No	Housekeeping Required	Open
24	28-Jun-23	Wed	Jun	45	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
25	28-Jun-23	Wed	Jun	46	Housekeeping	Unsafe Condition	No	Needed at height on scaffolds - undertaken during the night	Closed
26	28-Jun-23	Wed	Jun	47	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
27	30-Jun-23	Fri	Jun	48	Housekeeping	Unsafe Condition	No	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
28	01-Jul-23	Sat	Jul	49	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
29	01-Jul-23	Sat	Jul	50	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
30	01-Jul-23	Sat	Jul	50	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
Sheet1

Cell Formulas
Range	Formula
K1	K1	=COUNT(FILTER(AllObs1,(AllObs1[Column5]="Housekeeping")))
K2	K2	=COUNT(FILTER(AllObs1,(AllObs1[Column5]=L2)*(AllObs1[Column7]="Yes")))
K3	K3	=COUNT(FILTER(AllObs1,((AllObs1[Column5]="Work at Height")+(AllObs1[Column5]="Housekeeping"))*(AllObs1[Column9]="Closed")))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$30:$I$30	K1:K3

Is it what you're looking into? CMIIW

FYR

Hi RudRud
Thanks for such a prompt reply appreciated.
The two formula in K1 and K2 work fine apart from the fact that it is counting the number of entries twice.
E.g., Housekeeping in the main table is 208 times and formula gives me 416, Working at height with a yes is 17 but formula gives me 34.
Can you help?
Thanks
William

 

[RudRud]

Hi RudRud
Thanks for such a prompt reply appreciated.
The two formula in K1 and K2 work fine apart from the fact that it is counting the number of entries twice.
E.g., Housekeeping in the main table is 208 times and formula gives me 416, Working at height with a yes is 17 but formula gives me 34.
Can you help?
Thanks
William

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9		16	Housekeeping
2	27-Jun-23	Tue	Jun	23	Housekeeping	Unsafe Condition	Yes	Some areas required attention, Bailey Bridge area, under Piperack 1 at grade, 1st lift scaffold South of stack duct ISBL.	Open		7	Work at Height
3	27-Jun-23	Tue	Jun	24	Housekeeping	Unsafe Condition	Yes	Water bottles all over the site - picked up and disposed off in the bags and skips	Closed		18
4	28-Jun-23	Wed	Jun	25	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
5	28-Jun-23	Wed	Jun	26	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
6	28-Jun-23	Wed	Jun	27	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
7	30-Jun-23	Fri	Jun	28	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
8	1-Jul-23	Sat	Jul	29	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
9	1-Jul-23	Sat	Jul	30	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
10	28-Jun-23	Wed	Jun	31	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
11	28-Jun-23	Wed	Jun	32	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
12	28-Jun-23	Wed	Jun	33	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
13	30-Jun-23	Fri	Jun	34	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
14	1-Jul-23	Sat	Jul	35	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
15	1-Jul-23	Sat	Jul	36	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
16	27-Jun-23	Tue	Jun	37	Housekeeping	Unsafe Condition	NO	Some areas required attention, Bailey Bridge area, under Piperack 1 at grade, 1st lift scaffold South of stack duct ISBL.	Open
17	27-Jun-23	Tue	Jun	38	Housekeeping	Unsafe Condition	NO	Water bottles all over the site - picked up and disposed off in the bags and skips	Closed
18	28-Jun-23	Wed	Jun	39	Work at Height	Unsafe Condition	NO	Loose materials on scaffolds at height - removed in the shift	Closed
19	28-Jun-23	Wed	Jun	40	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
20	28-Jun-23	Wed	Jun	41	Environmental Health	Unsafe Condition	No	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
21	30-Jun-23	Fri	Jun	42	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
22	1-Jul-23	Sat	Jul	43	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
23	1-Jul-23	Sat	Jul	44	Work at Height	Unsafe Condition	No	Housekeeping Required	Open
24	28-Jun-23	Wed	Jun	45	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
25	28-Jun-23	Wed	Jun	46	Housekeeping	Unsafe Condition	No	Needed at height on scaffolds - undertaken during the night	Closed
26	28-Jun-23	Wed	Jun	47	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
27	30-Jun-23	Fri	Jun	48	Housekeeping	Unsafe Condition	No	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
28	1-Jul-23	Sat	Jul	49	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
29	1-Jul-23	Sat	Jul	50	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
30	1-Jul-23	Sat	Jul	50	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
Sheet1

Cell Formulas
Range	Formula
K1	K1	=ROWS(FILTER(AllObs1,(AllObs1[Column5]="Housekeeping")))
K2	K2	=ROWS(FILTER(AllObs1,(AllObs1[Column5]=L2)*(AllObs1[Column7]="Yes")))
K3	K3	=ROWS(FILTER(AllObs1,((AllObs1[Column5]="Work at Height")+(AllObs1[Column5]="Housekeeping"))*(AllObs1[Column9]="Closed")))

Hi @William53 , Please change COUNT into ROWS instead, Apologies for the confusion

 

[William53]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9		16	Housekeeping
2	27-Jun-23	Tue	Jun	23	Housekeeping	Unsafe Condition	Yes	Some areas required attention, Bailey Bridge area, under Piperack 1 at grade, 1st lift scaffold South of stack duct ISBL.	Open		7	Work at Height
3	27-Jun-23	Tue	Jun	24	Housekeeping	Unsafe Condition	Yes	Water bottles all over the site - picked up and disposed off in the bags and skips	Closed		18
4	28-Jun-23	Wed	Jun	25	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
5	28-Jun-23	Wed	Jun	26	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
6	28-Jun-23	Wed	Jun	27	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
7	30-Jun-23	Fri	Jun	28	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
8	1-Jul-23	Sat	Jul	29	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
9	1-Jul-23	Sat	Jul	30	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
10	28-Jun-23	Wed	Jun	31	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
11	28-Jun-23	Wed	Jun	32	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
12	28-Jun-23	Wed	Jun	33	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
13	30-Jun-23	Fri	Jun	34	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
14	1-Jul-23	Sat	Jul	35	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
15	1-Jul-23	Sat	Jul	36	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
16	27-Jun-23	Tue	Jun	37	Housekeeping	Unsafe Condition	NO	Some areas required attention, Bailey Bridge area, under Piperack 1 at grade, 1st lift scaffold South of stack duct ISBL.	Open
17	27-Jun-23	Tue	Jun	38	Housekeeping	Unsafe Condition	NO	Water bottles all over the site - picked up and disposed off in the bags and skips	Closed
18	28-Jun-23	Wed	Jun	39	Work at Height	Unsafe Condition	NO	Loose materials on scaffolds at height - removed in the shift	Closed
19	28-Jun-23	Wed	Jun	40	Housekeeping	Unsafe Condition	Yes	Needed at height on scaffolds - undertaken during the night	Closed
20	28-Jun-23	Wed	Jun	41	Environmental Health	Unsafe Condition	No	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
21	30-Jun-23	Fri	Jun	42	Housekeeping	Unsafe Condition	Yes	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
22	1-Jul-23	Sat	Jul	43	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
23	1-Jul-23	Sat	Jul	44	Work at Height	Unsafe Condition	No	Housekeeping Required	Open
24	28-Jun-23	Wed	Jun	45	Work at Height	Unsafe Condition	Yes	Loose materials on scaffolds at height - removed in the shift	Closed
25	28-Jun-23	Wed	Jun	46	Housekeeping	Unsafe Condition	No	Needed at height on scaffolds - undertaken during the night	Closed
26	28-Jun-23	Wed	Jun	47	Environmental Health	Unsafe Condition	Yes	Fireproofing shed not sealed off causing dust issue - completed during the night	Closed
27	30-Jun-23	Fri	Jun	48	Housekeeping	Unsafe Condition	No	Skips and bags need removing from west of ISBL - all done and emptied and removed	Closed
28	1-Jul-23	Sat	Jul	49	Housekeeping	Unsafe Condition	Yes	Housekeeping on Pipe Rack 1 needs to be undertaken - full of clips and bandings	Closed
29	1-Jul-23	Sat	Jul	50	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
30	1-Jul-23	Sat	Jul	50	Work at Height	Unsafe Condition	Yes	Housekeeping Required	Open
Sheet1

Cell Formulas
Range	Formula
K1	K1	=ROWS(FILTER(AllObs1,(AllObs1[Column5]="Housekeeping")))
K2	K2	=ROWS(FILTER(AllObs1,(AllObs1[Column5]=L2)*(AllObs1[Column7]="Yes")))
K3	K3	=ROWS(FILTER(AllObs1,((AllObs1[Column5]="Work at Height")+(AllObs1[Column5]="Housekeeping"))*(AllObs1[Column9]="Closed")))

Hi @William53 , Please change COUNT into ROWS instead, Apologies for the confusion

HI RudRud
Initially I just added "/2" to your formula and it got the result but the solution above works perfectly, thank you very much
Works fine now

 

[Fluff]

Did you try the formula I suggested?

 

[RudRud]

HI RudRud
Initially I just added "/2" to your formula and it got the result but the solution above works perfectly, thank you very much
Works fine now

Glad to assist, If you have any further questions, please don't hesitate to ask/share with us

 

[William53]

Did you try the formula I suggested?

Hi Fluff
My knowledge of the LET formula left me a bit nervous to follow it. So I read up on it and watched some videos and then applied it my Filtered formula and it works perfectly. Thank you for taking the time to enlighten me on the LET formula.

 

[Fluff]

You're welcome & thanks for the feedback.