Countifs question

[Sarasota]

Apologies in advance if this is a no brainer .. I am brain dead atm...

I have a SS with 3 columns of dates based on stages The assumption is if there is a date in one column and not another that it is still in that phase.

How can I combine 2 countifs to get the data needed below?

I need to combine two counts ifs

I need to get counts for L5, M5, N5, O5, P5 based off of the countifs in K5 and then the same for rows 5 - 8


Here is a sample table:

countifs-issues.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1												7/26/2023	6/25/2023	4/26/2023	1/26/2023	7/26/2022
2												6/26/2023	4/27/2023	1/27/2023	7/26/2022	1/1/1993
3											Count	< 30 days	31 - 90 Days	91 days - 180 days	181 - 365 days	1 year +	Check
4										Inventory (all stages)	50	4	4	15	9	18	50
5										Stage 2 (active)	13						0
6										Placed Stage 3	2						0
7										Stage 2 (stage / no stage 3)	12						0
8										Stage 3	23						0
9										Check2	50	0	0	0	0	0
10	Account	Desk	Stage1	Stage2	Stage3
11	S001	IJ	12/6/1993	5/4/1994	10/2/1995
12	S002	IJ	10/3/2001	1/8/2002
13	S003	IJ	8/22/2003		10/26/1995
14	S004	ALZ	5/5/2015	6/9/2015	12/26/2018
15	S005	ALZ	10/22/2015	11/23/2015	3/15/2016
16	S006	BH	12/16/2020	2/20/2023
17	S007	BH	1/8/2021
18	S008	BH	1/8/2021
19	S009	BH	1/8/2021
20	S010	BH	2/8/2021	11/30/2022
21	S011	IM	4/28/2021	8/25/2022	11/22/2022
22	S012	FJ	5/14/2021	1/17/2022	12/8/2022
23	S013	FJ	5/28/2021	5/4/2022	4/24/2023
24	S014	IM	6/30/2021	8/9/2021	12/16/2021
25	S015	FJ	2/22/2022	11/14/2022	2/10/2023
26	S016	IM	3/23/2022	5/3/2022	2/1/2023
27	S017	TLA	3/24/2022	5/17/2022	4/14/2023
28	S018	TLA	4/26/2022	7/6/2022	8/29/2022
29	S019	ALZ	11/18/2022	2/10/2023	5/31/2023
30	S020	ALZ	11/18/2022	2/10/2023
31	S021	SDF	11/18/2022	2/10/2023
32	S022	FJ	11/18/2022	2/10/2023	5/30/2023
33	S023	BH	11/24/2022	2/8/2023
34	S024	TLA	12/20/2022	1/18/2023
35	S025	TLA	12/20/2022	3/2/2023
36	S026	TLA	1/20/2023	5/15/2023
37	S027	ALZ	1/20/2023	3/23/2023
38	S028	ALZ	1/27/2023		10/17/2022
39	S029	TLA	1/31/2023	6/6/2023
40	S030	TLA	2/1/2023	4/26/2023	5/1/2023
41	S031	TLA	2/1/2023	5/1/2023
42	S032	TLA	2/1/2023	3/28/2023	5/30/2023
43	S033	ALZ	2/1/2023
44	S034	BH	2/13/2023
45	S035	FJ	2/15/2023	4/3/2023	6/5/2023
46	S036	FJ	2/15/2023	4/3/2023	6/28/2023
47	S037	FJ	2/15/2023	4/3/2023	6/26/2023
48	S038	FJ	2/15/2023	4/3/2023	6/5/2023
49	S039	FJ	2/15/2023	4/3/2023	6/19/2023
50	S040	FJ	2/22/2023	5/9/2023	7/11/2023
51	S041	FJ	2/27/2023	4/13/2023	6/27/2023
52	S042	FJ	2/27/2023	4/13/2023	6/24/2023
53	S043	BH	6/23/2023
54	S044	BH	6/23/2023
55	S045	BH	6/23/2023
56	S046	BH	6/23/2023
57	S047	BH	7/11/2023
58	S048	BH	7/11/2023
59	S049	BH	7/11/2023
60	S050	BH	7/11/2023
61
62
63
Sheet1

Cell Formulas
Range	Formula
L1	L1	=TODAY()
M1:O1	M1	=L2-1
P1	P1	=O2
L2	L2	=TODAY()-30
M2	M2	=TODAY()-90
N2	N2	=TODAY()-180
O2	O2	=TODAY()-365
L4:P4	L4	=COUNTIFS($C$11:$C$60,">="&L2,$C$11:$C$60,"<="&L1)
K4	K4	=COUNTIF(C11:C4693,"<>")
K5	K5	=COUNTIFS( D11:D60,"=", E11:E60,"=")
K6	K6	=COUNTIFS( C11:C60,"<>", D11:D60,"=", E11:E60,"<>")
K7	K7	=COUNTIFS(C10:C60,"<>", D10:D60,"<>", E10:E60, "=")
K8	K8	=COUNTIF(E11:E60,"<>")-K6
Q4:Q8	Q4	=SUM(L4:P4)
K9:P9	K9	=SUM(K5:K8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K9:P9	Cell Value	=K4	text	YES
K9:P9	Cell Value	<>K4	text	NO
Q4:Q8	Cell Value	=K4	text	YES
Q4:Q8	Cell Value	<>50+K4	text	NO

Thank you in advance for any help!

 

[Sarasota]

Thanks to all that looked I figured it out. Just continue the countif statement.

=COUNTIFS(G11:G4693,"<>",H11:H4693,"<>", I11:I4693,"=")
Isolated the specific accounts

=COUNTIFS($G$11:$G$4693,"<>",$H$11:$H$4693,"<>", $I$11:$I$4693,"=",$H$11:$H$4693,">="&U1,$H$11:$H$4693,"<="&U2)
segmented the isolated accounts by dates (U1 = before date and U2 = after date on a cheat table)

Yes, I know there is probably an easier way to get this but this was short and sweet and got my report completed.