Lookup & Sum values between specific date periods

[Izzy10]

Hi everyone,

Please see below sample data set.
The columns are always going to be the same, but the rows will be dynamic each time a report is run.

Book1.xlsx
	A	B	C	D	E	F	G
1	Start	01-May
2	End	07-May
3
4	No	Name	Date	Category	Type	Dr	Cr
5	15	Name1	01-May	Entertainment		-200
6	90	Name2	01-May	Supplies			1500
7	45	Name3	01-May	Travel		-300
8	50	Name4	02-May	Supplies			50000
9	55	Name5	02-May	Travel Car			35
10	57	Name6	03-May	Supplies			20
11	56	Name7	03-May	Travel			30
12	60	Name8	05-May	Supplies			3110
13	63	Name9	05-May	Supplies			90
14	65	Name10	10-May	Entertainment		-50
15	66	Name11	12-May	Supplies			520
Data

"Data" sheet:
Sum of specified categories (Column D) - the amounts could be in either in Column F or Column G
Only for a specified date - between Start (B1) & End (B2)

Book1.xlsx
	A	B	C
3	Category		Total
4	Entertainment		-200
5	Travel		-235
6	HR		0
Calculation

"Calculation" sheet:
The sum to be calculated on this sheet

Additional notes:
If there is a category that is not in Column D of "Data sheet" (e.g. in this example "HR"), Column C of "Calucation" needs to show 0
Formula to capture data if the cell contains the words in Column D (e.g. if the category = Travel Car, it should still be included in the calculation)

Thank you!

 

[Izzy10]

My apologies, I didn't mention in the original post that I was seeking help for a formula to calculate the sum. Thank you.

 

[kevin9999]

I'm sure there must be a simpler method than this, but this might do until smarter people than me give you a more elegant solution (just adjust the sum range length to cover all possible values)

filter.xlsm
	A	B	C	D
1	Category		Total
2	Entertainment		-200
3	Travel		-235
4	HR		0
5
Calculation

Cell Formulas
Range	Formula
C2:C4	C2	=SUMIFS(Data!$F$5:$F$1000,Data!$C$5:$C$1000,">="&Data!$B$1,Data!$C$5:$C$1000,"<="&Data!$B$2,Data!$D$5:$D$1000,"*"&Calculation!A2&"*")+SUMIFS(Data!$G$5:$G$1000,Data!$C$5:$C$1000,">="&Data!$B$1,Data!$C$5:$C$1000,"<="&Data!$B$2,Data!$D$5:$D$1000,"*"&Calculation!A2&"*")