Identify product that have different values with a spilled formula

[ED38]

Hello All,

Once again I am requesting your support

FOr a list of product in cell col G, I have a list of associated values in col H and J with a number of occurences different from a product code to another.
I would like a spilled formula to identify the product that have always the same value1 and value2 (like product A111, B803) vs those having different values (like A063, b476, A080...).
I thought about using the standard deviation (STDEV.P) to do that but have hard time to make it work, so coming to you...
if you have other idea , no pb, stdev.p is just a proxi here.

thank you

Book3
	G	H	I	J	K
1	Product	Value1	value2	Sum of the 2 STDev.P
2	A063	787	0	560,8986837	could a spilled formula do that?
3	A063	818	0	560,8986837
4	A063	1560	0	560,8986837
5	A063	2223	0	560,8986837
6	A063	1396	0	560,8986837
7	A063	1427	0	560,8986837
8	A063	1843	0	560,8986837
9	A063	2617	0	560,8986837
10	A063	1669	0	560,8986837
11	B476	1302	10	289,8295835
12	B476	1339	10	289,8295835
13	B476	1456	0	289,8295835
14	B476	2022	0	289,8295835
15	A111	50	0	0
16	A111	50	0	0
17	B803	316	0	0
18	A110	164	0	0
19	A080	52	0	21,5
20	A080	9	0	21,5
21	A081	0	100	50
22	A081	0	200	50
Sheet1

Cell Formulas
Range	Formula
J2	J2	=STDEV.P(H2:H10)+STDEV.P(I2:I10)
J3:J10,J12:J14,J16,J20,J22	J3	=J2
J11	J11	=STDEV.P(H11:H14)+STDEV.P(I11:I14)
J15,J19,J21	J15	=STDEV.P(H15:H16)+STDEV.P(I15:I16)
J17:J18	J17	=STDEV.P(H17)+STDEV.P(I17)

 

[DazCD]

Not sure what you mean,

this? In Col J2 copied down

=SUMIF(H2:H22,UNIQUE(H2:H22),I2:I22)

 

[ED38]

Hi DazCD,

Thank you for your attempt, but unfortunately it does not match, actually I am expecting a spilled formula what would :
- either provide the same resulat as the values in column J
- either a false when the value is >0 in J and true when the value =0 in J
does it clarify?

 

[ED38]

but you gave me an idea using unique and I solved it myself , thank you
here is my solution in cell K2 : (no need to use the stdev calculation on top!)

Book3
	G	H	I	J	K
1	Product	Value1	value2	Sum of the 2 STDev.P	could a spilled formula do that?
2	A063	787	0	560,8986837	FALSE
3	A063	818	0	560,8986837	FALSE
4	A063	1560	0	560,8986837	FALSE
5	A063	2223	0	560,8986837	FALSE
6	A063	1396	0	560,8986837	FALSE
7	A063	1427	0	560,8986837	FALSE
8	A063	1843	0	560,8986837	FALSE
9	A063	2617	0	560,8986837	FALSE
10	A063	1669	0	560,8986837	FALSE
11	B476	1302	10	289,8295835	FALSE
12	B476	1339	10	289,8295835	FALSE
13	B476	1456	0	289,8295835	FALSE
14	B476	2022	0	289,8295835	FALSE
15	A111	50	0	0	TRUE
16	A111	50	0	0	TRUE
17	B803	316	0	0	TRUE
18	A110	164	0	0	TRUE
19	A080	52	0	21,5	FALSE
20	A080	9	0	21,5	FALSE
21	A081	0	100	50	FALSE
22	A081	0	200	50	FALSE
Sheet1

Cell Formulas
Range	Formula
K2:K22	K2	=BYROW(G2:G22,LAMBDA(x,IF(ROWS(UNIQUE(FILTER(G2:I22,G2:G22=x),FALSE))=1,TRUE,FALSE)))
J2	J2	=STDEV.P(H2:H10)+STDEV.P(I2:I10)
J3:J10,J12:J14,J16,J20,J22	J3	=J2
J11	J11	=STDEV.P(H11:H14)+STDEV.P(I11:I14)
J15,J19,J21	J15	=STDEV.P(H15:H16)+STDEV.P(I15:I16)
J17:J18	J17	=STDEV.P(H17)+STDEV.P(I17)
Dynamic array formulas.