compare several sums

[excelNewbie22]

hi!
all the ranges are equal, why doesn't it return 1?

test
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	1	4	7	9	11	12		1-2-3-4-5-29		1	2	3	4	5	29
3	1	2	5	10	11	15
4
5										0
test

Cell Formulas
Range	Formula
L2:Q2	L2	=TRIM(MID(SUBSTITUTE(J2,"-",REPT(" ",99)),SEQUENCE(,6,,99),99))
L5	L5	=IF(AND(SUM($C$2:$H$2=$C$3:$H$3),(SUM($C$3:$H$3=L2:Q2))),1,0)
Dynamic array formulas.

 

[Fluff]

You cannot sum boolean values (ie True & False) so both sums come to 0 which is false.

 

[excelNewbie22]

"boolean values" because i used IF ?

can you please help me with a better solution?

 

[Fluff]

What exactly are your trying to do. Providing a formula that does not work & no explanation is not very helpful.

 

[excelNewbie22]

i want to check if the sum of c2:h2 is equal to c3:h3 and also equal to l2:q2

 

[Fluff]

That will never be true as the "numbers" in L2:Q2 are text

 

[excelNewbie22]

so, is there a better formula to split j2 into numbers only in separated cells (without the hyphens) so i could compare the sums?

 

[Fluff]

How about

=IF(AND(SUM($C$2:$H$2)=SUM($C$3:$H$3),SUM($C$3:$H$3)=SUM(--L2:Q2)),1,0)

 

[excelNewbie22]

very good, thanks a lot!

 

[Fluff]

Glad to help & thanks for the feedback.