Filter Rows by Criteria

[TheMacroNoob]

Hello excel experts,

I have annoying (crappy) data, and I am trying to grab all names from a column that meets a couple criteria.

In that column are subtotal rows with a count of names and a TOTAL heading, and category headings like:
- 2020 Sold Properties
- 2020 Completed Refi
- 2021 Resyndication

There are 20+ such unique examples of subheadings that I don't want to pull into my list. I would like to exclude all of them.

Here is an example of such a problem, and my current formula that doesn't exclude these total rows. I thought by including the YEAR (which I need anyway) criteria, it would remove such rows because there isn't a value there for those, but it did not.

NameX, ExampleX, StateX, are all placeholders, the actual file has specific names.

Sale Forecast Comparison.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
5
6										Problem	Desired Result
7		406	Name1	Example1	State1	172	Sold	1/6/20		Name1	Name1
8		380	Name2	Example2	State2	242	Sold	1/30/20		Name2	Name2
9		576	Name3	Example3	State3	228	Sold	3/6/20		Name3	Name3
10		597	Name4	Example4	State4	124	Sold	3/11/20		Name4	Name4
11		254	Name5	Example5	State5	288	Sold	3/11/20		Name5	Name5
12		605	Name6	Example6	State6	298	Sold	3/30/20		Name6	Name6
13		635	Name7	Example7	State7	274	Sold	3/31/20		Name7	Name7
14			Name8	Example8	State8	196	Sold	4/29/20
15			Name9	Example9	State9	150	Signed	4/30/20
16			Name10	Example10	State10	246	Sold	5/18/20
17			Name11	Example11	State11	180	Sold	6/24/20
18		117	Name12	Example12	State12	64	Signed	7/30/20		Name12	Name12
19		468	Name13	Example13	State13	100	Sold	7/31/20		Name13	Name13
20		676	Name14	Example14	State14	292	Pending	8/31/20		Name14	Name14
21		922	Name15	Example15	State15	100	Pending	7/31/20		Name15	Name15
22		15	TOTAL SOLD			2,564				TOTAL SOLD
23
Sheet4

Cell Formulas
Range	Formula
K7:K22	K7	=IFS(ISBLANK($C$7:$C$22),"",OR(YEAR($I$7:$I$22)=2020),D7:D22)
Dynamic array formulas.

 

[Fluff]

How about

=IFS($C$7:$C$22="","",YEAR($I$7:$I$22)=2020,D7:D22,1,"")