If statement for calculate value from cell criteria and between two dates

[kitsa]

Hi,
I'm confused on how I would write an if statement on this issue. I have a cell with e.g. AW018 in cell (D6) and I have dates across rows (2:2). I'm trying to find the value of AW018 (F6:L6) if I use two dates e.g. 11/03/22 to 16/03/22, should equal $8,075.00
Can someone please help how do I write this?

 

[kevin9999]

One option would be a Sumproduct(). In the example below, I only manually entered enough data for demonstration purposes. You'll need to adjust the ranges D3:D7 and F3:L7 to suit your actual ranges.

hide.xlsb
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Site	Client	Job Code	Rate	Cost	Cost	Cost	Cost	Cost	Cost	Cost
2	Date				10/03/22	11/03/22	12/03/22	13/03/22	14/03/22	15/03/22	16/03/22
3			AD006	$ 95.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -
4			AS002	$ 95.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -
5			AS003	$ 105.00	$ 3,360.00	$ 2,520.00	$ -	$ -	$ 1,260.00	$ -	$ -
6			AW018	$ 95.00	$ 2,090.00	$ 1,520.00	$ 1,900.00	$ -	$ 1,615.00	$ 1,520.00	$ 1,520.00		Job Code	Start	End	Total
7			AW021	$ 100.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -		AW018	11/03/22	16/03/22	$ 8,075.00
8
Sheet4

Cell Formulas
Range	Formula
Q7	Q7	=SUMPRODUCT((D3:D7=N7)*(F2:L2>=O7)*(F2:L2<=P7)*(F3:L7))

 

[kitsa]

Thanks so much Kevin, it worked perfectly.

 

[kevin9999]

Thanks so much Kevin, it worked perfectly.

You're welcome, and thanks for the feedback