Formula for if 2 criteria are met

[Trie1977]

Hi there,

Played around now for some days.
Hope to find an answer here, and I would be very happy.

Count.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	I am not able to combine A10 with A12 for example	Formula tried	Should Be
3	Pips+ & OI +	9	9	why this seems to work?	Query 1 : count how many times it occures that the week closed positive AND the Open Interest was higher than the previous week.
4	Pips- & OI +	52	13	and why not this?	Query 2 : count how many times it occures that the week closed negative AND the Open Interest was higher than the previous week.
5	Pips+ & OI lower			i have no clue	Query 3 : count how many times it occures that the week closed positive AND the Open Interest was lower than the previous week.
6	Pips- & OI lower			i have no clue	Query 4 : count how many times it occures that the week closed negative AND the Open Interest was lower than the previous week.
7	Pips- & OI lower streak			i have no clue	Query 5 : count how many times it occures that the week closed negative AND the Open Interest was higher for 2 weeks in a row.
8
9	Already figured out
10	Pips+	31			Query 6 : count how many times bars closed higher
11	Pips-	30			Query 7 : count how many times bars closed lower
12	OI Higher Streaks >1	6			Query 8 : count how many times it occures that the Open Interest was higher for 2 weeks in a row.
13	OI Lower Streaks >1	9			Query 9 : count how many times it occures that the Open Interest was lower for 2 weeks in a row.
Formulas

Cell Formulas
Range	Formula
B3	B3	=COUNTIFS('GBPUSD10080 Result after'!$J$5:$J$65,">1"&SUMPRODUCT(--('GBPUSD10080 Result after'!$N$5:$N$65>'GBPUSD10080 Result after'!$N$6:$N$66)))
B4	B4	=COUNTIFS('GBPUSD10080 Result after'!$J$5:$J$65,"<1"&SUMPRODUCT(--('GBPUSD10080 Result after'!$N$5:$N$65>'GBPUSD10080 Result after'!$N$6:$N$66)))
B10	B10	=COUNTIFS('GBPUSD10080 Result after'!$J$5:$J$65,">0")
B11	B11	=COUNTIFS('GBPUSD10080 Result after'!$J$5:$J$65,"<0")
B12	B12	=SUM(--(FREQUENCY( IF('GBPUSD10080 Result after'!$N$5:$N$65>'GBPUSD10080 Result after'!$N$6:$N$66, ROW('GBPUSD10080 Result after'!$N$5:$N$65)), IF('GBPUSD10080 Result after'!$N$5:$N$65<='GBPUSD10080 Result after'!$N$6:$N$65, ROW( 'GBPUSD10080 Result after'!$N$5:$N$65))) >1))
B13	B13	=SUM(--(FREQUENCY( IF('GBPUSD10080 Result after'!$N$5:$N$65<'GBPUSD10080 Result after'!$N$6:$N$66, ROW('GBPUSD10080 Result after'!$N$5:$N$65)), IF('GBPUSD10080 Result after'!$N$5:$N$65>='GBPUSD10080 Result after'!$N$6:$N$65, ROW( 'GBPUSD10080 Result after'!$N$5:$N$65))) >1))
Press CTRL+SHIFT+ENTER to enter array formulas.

DataSheet;

Count.xlsx
	B	C	D	E			H		J	K	L		N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
2															BRITISH POUND - CHICAGO MERCANTILE EXCHANGE						USD INDEX - ICE FUTURES U.S.
3
4	COT Get	COT Date	Start Candle	Open			Close		ClosesPips	Up	Down		OI GBP	OI USD	GBP Long	GBP Short	Total	GBP % L	GBP % S	Net Pos.	USD Long	USD Short	Total	USD % Long	USD % Short	Net Pos.
5	######	######	2022.02.27	####			####		-78	128	-107		211869	56651	47679	48016	####	49,82%	50,18%	-337	43761	8987	####	83%	17%	34774
6	######	######	2022.02.20	####			####		-177	53	-313		188443	54922	42249	48058	####	46,78%	53,22%	-5809	43726	7642	51368	85%	15%	36084
7	######	######	2022.02.13	####			####		44	92	-64		195302	54283	50151	47914	####	51,14%	48,86%	2237	42349	6963	49312	86%	14%	35386
8	######	######	2022.02.06	####			####		27	111	-42		197948	53603	44709	53254	####	45,64%	54,36%	-8545	40370	6605	####	86%	14%	33765
9	######	######	2022.01.30	####			####		134	235	-6		184007	56477	29597	53202	####	35,75%	64,25%	-23605	43897	9326	####	82%	18%	34571
10	######	######	2022.01.23	####			####		-156	14	-193		182040	52328	36666	44429	####	45,21%	54,79%	-7763	41772	4911	####	89%	11%	36861
11	######	######	2022.01.16	####			####		-121	17	-128		183234	53283	39780	40007	####	49,87%	50,15%	-247	42369	5935	####	88%	12%	36434
12	######	######	2022.01.09	####			####		103	169	-48		#####	57218	30506	59672	####	33,83%	66,17%	-29166	45872	7980	####	85%	15%	37892
13	######	######	2022.01.02	####			####		62	69	-99		#####	58089	25980	65151	91131	28,51%	71,49%	-39171	47750	8672	####	85%	15%	39078
14	######	######	2021.12.26	####			####		150	158	-3		#####	57892	23093	73812	####	23,83%	76,17%	-50719	47169	10380	####	49%	11%	36789
15	######	######	2021.12.19	####			####		152	198	-66		#####	56162	20824	78510	####	20,96%	79,04%	-57686	46222	11107	####	47%	11%	35115
16	######	######	2021.12.12	####			####		-12	128	-75		213210	52098	29497	80245	####	26,88%	73,12%	-50748	42263	11022	####	39%	10%	31241
17	######	######	2021.12.05	####			####		45	71	-57		238310	59994	48950	87227	####	35,95%	64,05%	-38277	43058	8193	51251	32%	6%	34865
18	######	######	2021.11.28	####			####		-102	38	-138		#####	52172	52099	90998	####	36,41%	63,59%	-38899	42010	6131	48141	29%	4%	35879
19	######	######	2021.11.21	####			####		-120	2	-174		213313	57694	50122	84701	####	37,18%	62,82%	-34579	47116	11341	####	35%	8%	35775
20	######	######	2021.11.14	####			####		36	107	-11		#####	59387	50443	82042	####	38,07%	61,93%	-31599	47959	13051	61010	36%	10%	34908
21	######	######	2021.11.07	####			####		-64	130	-124		186898	57713	54004	66097	####	44,97%	55,03%	-12093	46521	11073	####	39%	9%	35448
22	######	######	2021.10.31	####			####		-180	24	-251		166780	57947	57255	42208	####	57,56%	42,44%	15047	45395	10413	####	46%	10%	34982
23	######	######	2021.10.24	####			####		-49	87	-75		156495	58993	51912	36959	####	58,41%	41,59%	14953	45189	10732	55921	51%	12%	34457
24	######	######	2021.10.17	####			####		1	79	-46		158961	60731	49112	47497	####	50,84%	49,16%	1615	47568	11634	####	49%	12%	35934
25	######	######	2021.10.10	####			####		131	158	-46		172829	62836	46794	58773	####	44,33%	55,67%	-11979	49394	14332	####	47%	14%	35062
26	######	######	2021.10.03	####			####		58	98	-27		182338	57194	48137	68155	####	41,39%	58,61%	-20018	44715	12689	####	38%	11%	32026
GBPUSD10080 Result after

Cell Formulas
Range	Formula
J5:J26	J5	=(H5-E5)/10
K5:K26	K5	=(F5-E5)/10
L5:L26	L5	=(G5-E5)/10
Y14:Y26,S5:S26	S5	=IF(P5="","",P5/$R5)
Z14:Z26,T5:T26	T5	=IF(P5="","",Q5/$R5)
Y5:Y13	Y5	=IF(V5="","",V5/$X5)
Z5:Z13	Z5	=IF(V5="","",W5/$X5)
X14:X26,R14:R26	R14	=P14+Q14
AA14:AA26,U14:U26	U14	=P14-Q14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q5:AA13	Cell Value	<Q6	text	NO
Q5:AA13	Cell Value	>Q6	text	NO
P6:P13	Cell Value	<P7	text	NO
P6:P13	Cell Value	>P7	text	NO
P5,P14:AA64	Cell Value	<P6	text	NO
P5,P14:AA64	Cell Value	>P6	text	NO
O5:O64	Cell Value	<$O6	text	NO
O5:O64	Cell Value	>$O6	text	NO
N5:N64	Cell Value	<$N6	text	NO
N5:N64	Cell Value	>$N6	text	NO
L5:L65	Cell Value	<0	text	NO
L5:L65	Cell Value	>0	text	NO
K5:K65	Cell Value	<0	text	NO
K5:K65	Cell Value	>0	text	NO
J5:J65	Cell Value	<0	text	NO
J5:J65	Cell Value	>0	text	NO

Thank anyways for your time.
Tony.

 

[Trie1977]

Thank you so much. I will study this closer. At first glance it works perfectly fine now.

On last thing...

As time goes by, more rows will come. What is the best approach to incluse those rows?

• Give the table a name and always refer to that table name instead of the row numbers?
• Simply change the rows in the formula to let's say 5-100000 & 6-100001?

 

[KRice]

You’re welcome…I’m happy to help. As time goes by, I think there are two approaches to consider. One is to highlight the formulas that need to be changed and then do a search and replace all to change the ranges. Another option, probably better, is to use the Name Manager to give short but meaningful names to each of the ranges and then revise the formulas to use the Range Names. This will shorten the formulas considerably and make them easier to understand; and any future revisions to the size of the ranges would involve making just a few changes in the Name Manager while the formulas remain unchanged. The other option you mentioned—converting the data table to a formal table and then using structured references—wouldn’t be advisable in this case because your formulas use an offset on the OI column in some places, so any reference to OI would give the entire table column rather than the offset. The Named Range approach overcomes this issue.

 

[KRice]

To clarify my last post, here is an example where I've defined three named ranges in the Name Manager and then used Find/Replace All in the formulas to change the references to these Named Ranges. In the future, any changes in the length of the data table can be accommodated by editing these Named Ranges in the Name Manager, while the formulas will remain unchanged.

MrExcel_20220310_Trie.xlsx
	A	B	C
2		Results
3	Pips+ & OI higher	6	Query 1: count how many times the week closed positive AND the Open Interest was higher than the previous week
4	Pips- & OI higher	5	Query 2: count how many times the week closed negative AND the Open Interest was higher than the previous week
5	Pips+ & OI lower	6	Query 3: count how many times the week closed positive AND the Open Interest was lower than the previous week
6	Pips- & OI lower	5	Query 4: count how many times the week closed negative AND the Open Interest was lower than the previous week
7	Pips- & OI lower streak >=2	2	Combo Query 5A: count how many times the week closed negative AND there was a streak of at least 2 consecutive weeks where Open Interest was higher (AND applies to all elements in streak)
8	Pips+ & OI higher streak >=2	1	Combo Query 5B: count how many times the week closed positive AND there was a streak of at least 2 consecutive weeks where OI was higher (AND applies to all elements in streak)
9	Pips+ & OI lower streak >=2	2	Combo Query 5C: count how many times the week closed positive AND there was a streak of at least 2 consecutive weeks where Open Interest was lower (AND applies to all elements in streak)
10	Pips- & OI lower streak >=2	1	Combo Query 5D: count how many times the week closed negative AND there was a streak of at least 2 consecutive weeks where OI was lower (AND applies to all elements in streak)
11
12	Pips+	12	Query 6: count how many times bars closed higher
13	Pips-	10	Query 7: count how many times bars closed lower
14	OI Higher Streaks >1	2	Query 8: count how many streaks of at least 2 consecutive weeks occur where Open Interest was higher than the previous week
15	OI Lower Streaks >1	4	Query 9: count how many streaks of at least 2 consecutive weeks occur where Open Interest was lower than the previous week
16		1	Query 10: count how many streaks of exactly 2 consecutive weeks occur where Open Interest was higher than the previous week
17		0	Query 11: count how many streaks of exactly 3 consecutive weeks occur where Open Interest was higher than the previous week
18		1	Query 12: count how many streaks of at least 3 consecutive weeks occur where Open Interest was higher than the previous week
19		0	Query 12: count how many streaks of exactly 4 consecutive weeks occur where Open Interest was higher than the previous week
20		0	Query 12: count how many streaks of exactly 5 consecutive weeks occur where Open Interest was higher than the previous week
21		1	Query 12: count how many streaks of exactly 6 consecutive weeks occur where Open Interest was higher than the previous week
Formulas (2)

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT(--(Close>0),--(OI>OIoffset))
B4	B4	=SUMPRODUCT(--(Close<0),--(OI>OIoffset))
B5	B5	=SUMPRODUCT(--(Close>0),--(OI<OIoffset))
B6	B6	=SUMPRODUCT(--(Close<0),--(OI<OIoffset))
B7	B7	=SUM(--(FREQUENCY(IF((Close<0)*(OI>OIoffset),ROW(OI)),IF(((Close>=0)+(OI<=OIoffset))>0,ROW(OI)))>1))
B8	B8	=SUM(--(FREQUENCY(IF((Close>0)*(OI>OIoffset),ROW(OI)),IF(((Close<=0)+(OI<=OIoffset))>0,ROW(OI)))>1))
B9	B9	=SUM(--(FREQUENCY(IF((Close>0)*(OI<OIoffset),ROW(OI)),IF(((Close<=0)+(OI>=OIoffset))>0,ROW(OI)))>1))
B10	B10	=SUM(--(FREQUENCY(IF((Close<0)*(OI<OIoffset),ROW(OI)),IF(((Close>=0)+(OI>=OIoffset))>0,ROW(OI)))>1))
B12	B12	=SUMPRODUCT(--(Close>0))
B13	B13	=SUMPRODUCT(--(Close<0))
B14	B14	=SUM(--(FREQUENCY(IF(OI>OIoffset,ROW(OI)),IF(OI<=OIoffset,ROW(OI)))>1))
B15	B15	=SUM(--(FREQUENCY(IF(OI<OIoffset,ROW(OI)),IF(OI>=OIoffset,ROW(OI)))>1))
B16	B16	=SUM(--(FREQUENCY(IF(OI>OIoffset,ROW(OI)),IF(OI<=OIoffset,ROW(OI)))=2))
B17	B17	=SUM(--(FREQUENCY(IF(OI>OIoffset,ROW(OI)),IF(OI<=OIoffset,ROW(OI)))=3))
B18	B18	=SUM(--(FREQUENCY(IF(OI>OIoffset,ROW(OI)),IF(OI<=OIoffset,ROW(OI)))>=3))
B19	B19	=SUM(--(FREQUENCY(IF(OI>OIoffset,ROW(OI)),IF(OI<=OIoffset,ROW(OI)))=4))
B20	B20	=SUM(--(FREQUENCY(IF(OI>OIoffset,ROW(OI)),IF(OI<=OIoffset,ROW(OI)))=5))
B21	B21	=SUM(--(FREQUENCY(IF(OI>OIoffset,ROW(OI)),IF(OI<=OIoffset,ROW(OI)))=6))

Named Ranges
Name	Refers To	Cells
Close	='GBPUSD10080 Result after'!$J$5:$J$65	B3:B10, B12:B13
OI	='GBPUSD10080 Result after'!$N$5:$N$65	B14:B21, B3:B10
OIoffset	='GBPUSD10080 Result after'!$N$6:$N$66	B14:B21, B3:B10

 

[Trie1977]

Thank you. I will try this.

I want to say how happy I am with your help. So kind.

Should I close this thread now here at the board?

 

[KRice]

I'm happy to hear that you've found this helpful. Typically, threads remain open so that you or others can respond with further inquiries about the topic or offer alternative approaches to address the issue.