populate & sum numeric data based on two dates and sheet name using options

[Abdo]

Hello
I have two sheets contains data .
what I want in OUTPUT sheet when write dates in D3,E3 and sheet name in F3 and select optionbutton1 or optiobutton2 (ACTIVE X)
then should show data from row7 by summing the numbers within two dates and sheet name and optionbuttons .
the dates should match with column B in any sheet and the optionbutton will match with column D
my format date DD/MM/YYYY

ABB.xlsm
	A	B	C	D	E
1	ITEM	DATE	REF	CONDITION	BALANCE
2	1	01/01/2023	RE45666	AA	1200.00
3	2	01/01/2023	RE45667	PP	2000.00
4	3	01/01/2023	RE45668	AA	2200.00
5	4	01/01/2023	RE45669	PP	1000.00
6	5	01/01/2023	RE45670	AA	1500.00
7	6	02/01/2023	RE45671	AA	2000.00
8	7	02/01/2023	RE45672	AA	2200.00
9	8	02/01/2023	RE45673	AA	2400.00
10	9	02/01/2023	RE45674	PP	2600.00
CCR

ABB.xlsm
	A	B	C	D	E
1	ITEM	DATE	REF	CONDITION	BALANCE
2	1	01/01/2023	CV455-900	PP	1,200.00
3	2	01/01/2023	CV455-901	PP	2,000.00
4	3	01/01/2023	CV455-902	AA	2,200.00
5	4	01/01/2023	CV455-903	PP	1,000.00
6	5	01/01/2023	CV455-904	PP	1,500.00
7	6	02/01/2023	CV455-905	PP	2,000.00
8	7	02/01/2023	CV455-906	AA	2,200.00
9	8	02/01/2023	CV455-907	AA	2,400.00
CVF

ABB.xlsm
	A	B	C	D	E	F
1
2				FROM DATE	TO DATE	SHEET NAME
3
4
5
OUTPUT

the result in row7 after writing into cells and select optionbutton

ABB.xlsm
	C	D	E	F
1
2		FROM DATE	TO DATE	SHEET NAME
3		01/01/2023	02/01/2023	CCR
4
5
6
7		ITEM	BALANCE
8		1	11500
OUTPUT

the data could be 4000 rows for each sheet
thanks in advance

 

[awoohaw]

with new file gives the same result !

NEW.xlsx
	A	B	C	D	E
1	ITEM	DATE	REF	CONDITION	BALANCE
2	1	01/01/2023	RE45666	AA	1200
3	2	01/01/2023	RE45667	PP	2000
4	3	01/01/2023	RE45668	AA	2200
5	4	01/01/2023	RE45669	PP	1000
6	5	01/01/2023	RE45670	AA	1500
7	6	02/01/2023	RE45671	AA	2000
8	7	02/01/2023	RE45672	AA	2200
9	8	02/01/2023	RE45673	AA	2400
10	9	02/01/2023	RE45674	PP	2600
CCR

NEW.xlsx
	A	B	C	D	E
1	ITEM	DATE	REF	CONDITION	BALANCE
2	1	01/01/2023	CV455-900	PP	1200
3	2	01/01/2023	CV455-901	PP	2000
4	3	01/01/2023	CV455-902	AA	2200
5	4	01/01/2023	CV455-903	PP	1000
6	5	01/01/2023	CV455-904	PP	1500
7	6	02/01/2023	CV455-905	PP	2000
8	7	02/01/2023	CV455-906	AA	2200
9	8	02/01/2023	CV455-907	AA	2400
CVF

NEW.xlsx
	C	D	E	F
1
2		FROM DATE	TO DATE	SHEET NAME
3	AA	01/01/2023	02/01/2023	CVF
4
5
6
7		ITEM	BALANCE
8		1	2200
9
OUTPUT

Cell Formulas
Range	Formula
D8	D8	=SUM(((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))>=D3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))<=E3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$D$2:$D$4000,CVF!$D$2:$D$4000))=C3))
E8	E8	=SUM(((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))>=D3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))<=E3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$D$2:$D$4000,CVF!$D$2:$D$4000))=C3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$E$2:$E$4000,CVF!$E$2:$E$4000))))

I cannot explain it.
I just copied all three of your mini worksheets above into a brand new workbook and got 6800 and 3

Book1
	A	B	C	D	E	F	G
1
2				FROM DATE	TO DATE	SHEET NAME
3			AA	2023-01-01	2023-01-02	CVF
4
5
6
7				ITEM	BALANCE
8				3	6800
9
OUTPUT

Cell Formulas
Range	Formula
D8	D8	=SUM(((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))>=D3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))<=E3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$D$2:$D$4000,CVF!$D$2:$D$4000))=C3))
E8	E8	=SUM(((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))>=D3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))<=E3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$D$2:$D$4000,CVF!$D$2:$D$4000))=C3)* ((CHOOSE(MATCH(F3,{"CCR";"CVF"},0),CCR!$E$2:$E$4000,CVF!$E$2:$E$4000))))

 

[awoohaw]

What I would like you to do is to again display the formula editor and look to see the values that are captured with the formula, for the one that give you a result of 2200.

 

[Abdo]

This is what I got

 

[Fluff]

Try changing SUM to SUMPRODUCT.

 

[Abdo]

that works , Thanks Fluff !

 

[Abdo]

thanks awoohaw for the formulas .

​

 

[awoohaw]

Try changing SUM to SUMPRODUCT.

@Abdo, are you using a 2010 version or a 2019 version in this situation? I may write this wrong, but SUM doesn't do the array mathematics like
SUMPRODUCT in earlier versions.

Thanks @Fluff for stepping in with that suggestion!

 

[Fluff]

are you using a 2010 version or a 2019 version

Makes no difference, only 2021 & 365 are dynamically aware.

 

[awoohaw]

Makes no difference, only 2021 & 365 are dynamically aware.

Not even using CNTL-SHFT-ENTR key stroke?

 

[Fluff]

You would have to use CSE (or sumproduct) on anything earlier than 2021