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

 

[Abdo]

this is what I got

abdo.xlsx
	C	D	E	F
2	condition	FROM DATE	TO DATE	SHEET NAME
3	AA	01/01/2023	02/01/2023	CCR
4
5
6
7		ITEM	BALANCE
8		1	2200
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]

you have semi colons in places where I have commas. I'm not sure if that is the difference or not, as I still have the same result with either format.
Did you confirm my format of the formula worked before you made that change?

What does formula tracker do in the CHOOSE function when you step through the formula? Does it select the correct worksheet range?

And, it could be date values. Maybe convert the dates to numbers and see how they work out?

 

[Abdo]

you have semi colons in places where I have commas.

it depends on version office , not all of versions are comma .

Did you confirm my format of the formula worked before you made that change?

I click to copy to clipboard from your picture to my sheet and just change comma .
sorry my bad about amounts should be 6800 , not 11500 as I mentioned in post # 9
but strangely will show as in picture when I check the formual !

And, it could be date values. Maybe convert the dates to numbers and see how they work out?

the same result .

 

[awoohaw]

What are the criteria that are present when you get the 6800?
Can you post an xl2bb of that scenario?

 

[awoohaw]

Also, have you updated, changed, or refreshed your data in cells CCR!A1:E9 and CVF!A1:E9? I am using the data in Post #1.
I think you are using a different set of numbers than me, as I do not see the sequence in the Formula dialog that looks like
0;0;0;0;0;2400;2200;0;0;0;2200;0;0 13 values at minimum.
on either worksheet:
CCR:
1200;2000;2200;1000;1500;2000;2200;2400;2600 (9 Values)
CVF:
1200;2000;2200;1000;1500;2000;2200;2400 (8 Values)

 

[Abdo]

What are the criteria that are present when you get the 6800?

sorry that was for CVF sheet , not CCR
based on picture in post#13 the calculation is right but that's doesn't happens in E8 just show 2200 whether CVF or CCR sheets !

I am using the data in Post #1

me too.

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
	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
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]

sorry that was for CVF sheet , not CCR
based on picture in post#13 the calculation is right but that's doesn't happens in E8 just show 2200 whether CVF or CCR sheets !

me too.

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
	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
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'm really interested in this part of your image in post 13:

Please send an xl2bb of this exact scenario, that cell selected and the formula showing in the formula bar. Including the criteria cells and the columns that are being queried. I think the debugging starts at this point. No where else.

 

[Abdo]

Please send an xl2bb of this exact scenario

how I can do that ?!
just I select cell E8 where is your formula and click function from bar and shows me as you marked by red !

 

[awoohaw]

how I can do that ?!
just I select cell E8 where is your formula and click function from bar and shows me as you marked by red !

@Abdo , I don't understand your xl2bb posts in post #16. I completely recreated what you have there and I get 6800 for the calculation.
Can you start a new workbook using your xl2bb data in post #16. And, see if you till get 2200. Because I certainly do not.

 

[Abdo]

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))))