Conditional IF + array

[stantheman007]

In the final column I would like an output '1' if some conditions hold, and 0 if they do not all hold. Here is the array formula I am using (starting in row2): {=IF(($A$2:$A$871=A2)*($G$2:$G$871=G2)*($I$2:$I$871=I2)*($K$2:$K$871>=(0.95*K2))*($K$2:$K$871<=(1.05*K2)), "1", "0")}

By this formula, ID 24 should have some 1s inputted, but it does not. This is also the case with other entries that should be 1s, but are outputted as 0s. I do not know why this is the case.

Any help would be greatly appreciated!

Thanks,
Stan

ID	Type	Region	Bed Size	Transaction Date	Standardized Vendor Name - Long	Standardized Item Description - Long	Sum Of Qty	Standardized UOM	Standardized Conversion Factor	Price Paid	Total Spend	%RANK	WR
297	Hospital	West	56	Jul-18	Cardinal Health Med Prod Hosp Supply/Scientific	(2) Cloths per package 7.5î x 7.5î	1	CS	48	108	108	1	10800	1
297	Hospital	West	56	Aug-18	Cardinal Health Med Prod Hosp Supply/Scientific	(2) Cloths per package 7.5î x 7.5î	1	CS	48	108	108	1	10800	1
17	Health System	South	0	Jul-18	Arthrex Inc	#2 FIBERSTICK FIBERWIRE 50IN	2	BX	5	250	500	1	50000	0
17	Health System	South	0	Aug-18	Arthrex Inc	#2 FIBERSTICK FIBERWIRE 50IN	1	EA	1	50	50	0.5	2500	0
24	Hospital	South	512	Jul-18	Arthrex Inc	#2 FIBERSTICK FIBERWIRE 50IN	10	EA	1	50	500	1	50000	0
24	Hospital	South	512	Aug-18	Arthrex Inc	#2 FIBERSTICK FIBERWIRE 50IN	2	EA	1	50	100	0.5	5000	0
24	Hospital	South	512	Sep-18	Arthrex Inc	#2 FIBERSTICK FIBERWIRE 50IN	3	BX	5	250	750	1	75000	0
24	Hospital	South	512	Sep-18	Arthrex Inc	#2 FIBERSTICK FIBERWIRE 50IN	11	EA	1	50	550	1	55000	0
40	Health System	West	538	Jul-18	Arthrex Inc	#2 FIBERSTICK FIBERWIRE 50IN	3	EA	1	46	138	0	0	0
40	Health System	West	538	Aug-18	Arthrex Inc	#2 FIBERSTICK FIBERWIRE 50IN	3	EA	1	46	138	0	0	0

 

[stantheman007]

Stan

 

[gigidica]

Did you try:
=IF(AND(A2=$A$2,G2=$G$2,I2=$I$2,$K$2*0.95<=K2,K2<=$K$2*1.05),1,0)
?

 

[stantheman007]

Did you try:
=IF(AND(A2=$A$2,G2=$G$2,I2=$I$2,$K$2*0.95<=K2,K2<=$K$2*1.05),1,0)
?

Just tried and doesn't work. Thanks anyway.

 

[Fluff]

Maybe

=--(COUNTIFS($A$2:$A$871,A2,$G$2:$G$871,G2,$I$2:$I$871,I2,$K$2:$K$871,">="&(0.95*K2),$K$2:$K$871,"<="&(1.05*K2))>1)

 

[stantheman007]

$871,"<="&(1.05*K2))>1)

(COUNTIFS($A$2:$A$871,A2,$G$2:$G$871,G2,$I$2:$I$871,I2,$K$2:$K$871,">="&(0.95*K2),$K$2:$K$871,"<="&(1.05*K2))>1)

Legend!! This has saved me a massive amount of time & stress. Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback.