Sum if Between Dates and in the list

[p9j123]

Can someone help me with a formula for with the following requirements?

On Sheet 1, sum Column H if Column A is between Start Date and End Date, and if Column B is any of the list in Sheet 2 Column A.


Sheet2 (Column A

Red
Blue
Green

 

[KRice]

Here is one idea:

MrExcel_20220805.xlsx
	A	B						H	I	J	K
1	Date	Sheet2 Code						Amt		Start Date	1/16/2022
2	1/15/2022	Red						1		End Date	2/21/2022
3	1/23/2022	Blue						2		Sum	11
4	1/30/2022	Purple						3
5	1/17/2022	Green						9
6	3/13/2022	Purple						8
7	2/22/2022	Red						7
Sheet1

Cell Formulas
Range	Formula
K3	K3	=SUMPRODUCT((B2:B7=TRANSPOSE(Sheet2!A1:A3))*(A2:A7>=K1)*(A2:A7<=K2)*(H2:H7))

MrExcel_20220805.xlsx
	A
1	Red
2	Blue
3	Green
Sheet2

 

[p9j123]

Thank you so much, should I run it with CTRL+SHIFT+ENTER? or just [ENTER]?

 

[KRice]

Try just Enter and see if SUMPRODUCT handles the arrays… if no result, go back into the formula bar, hit F2, then Ctrl-Shift-Enter.

 

[Dave Patton]

T202208a.xlsm
	A	B						H	I	J	K
1	Date	Code						Amount		Start Date	16-Jan-2022
2	15-Jan-2022	Red						1		End Date	21-Feb-2022
3	23-Jan-2022	Blue						2		Sum	11
4	30-Jan-2022	Purple						3
5	17-Jan-2022	Green						9
6	13-Mar-2022	Purple						8
7	22-Feb-2022	Red						7
2c

Cell Formulas
Range	Formula
K3	K3	=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B7,Sheet2!A1:A3,0))),--(A2:A7>=K1),--(A2:A7<=K2),H2:H7)