Return the Sum of a set of Data based on multiple dynamic criteria

[sopphiron]

Hi,

I have a set of data similar to what you can see below:

Book1
	K
5
Sheet1

I want to filter the data in "Purchase Records" based on the 3 filters of year season, month, with overall count and price being displayed in the "Filtered Purchase Records" table, I can use the sumifs function but what I want is that, the filter should be dynamic in a sense that, if I don't enter any of the 3 filters the data be displayed based on the remaining two. for instance:

if I enter the year 2021, and leave season and month filters empty, all of the purchased pens and papers be displayed in the filtered table
or
if I enter 2021 and april, the results be filtered based on this criteria
or
if I enter spring, and leave year and month empty, the smaller table shows the purchased items in the spring of 2021 and 2022.

 

[sopphiron]

The table is shown here:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2						Purchase Records
3		no	year	season	month	product	count	unit price	total price		Filters	year	season	month
4		1	2021	spring	april	paper	20	50	1000
5		2	2021	spring	april	pen	10	5	50
6		3	2021	spring	may	paper	15	50	750				Filtered Purchase Records
7		4	2022	spring	april	paper	17	51	867		no	product	count	unit price	total price
8		5	2022	spring	may	pen	20	5	100		1	paper
9		6	2022	spring	may	pen	30	5	150		2	pen
10		7	2022	spring	june	paper	20	60	1200		3
11		8	2022	spring	june	paper	21	60	1260		4
12		9	2022	winter	january	pen	20	6	120		5
13		10	2022	winter	february	pen	30	6	180		6
14											7
15											8
16											9
17											10
18
Sheet1

Cell Formulas
Range	Formula
L8:L9	L8	=UNIQUE(F4:F13)
I4:I13	I4	=H4*G4
Dynamic array formulas.