Help simplify my formula!

**missxmelon** · 03-28-2013, 10:27 AM

Can someone please help me simplify my formula (see below)?

=IF(COUNTA(CO8:CQ8)=SUM(CO8:CQ8),"TRUE",IF(AND(COUNTA(CO8:CQ8)>SUM(CO8:CQ8),SUM(CO8:CQ8)>0,COUNTIF(CO8:CQ8,"EMPTY")>0,COUNTIF(CO8:CQ8,"M2")=0,COUNTIF(CO8:CQ8,"M1")=0,COUNTIF(CO8:CQ8,"A")=0),"TRUE",IF(COUNTA(CO8:CQ8)=COUNTIF(CO8:CQ8,"A"),"TRUE",IF(AND(COUNTA(CO8:CQ8)>COUNTIF(CO8:CQ8,"A"),COUNTIF(CO8:CQ8,"A")>0,COUNTIF(CO8:CQ8,"EMPTY")>0,COUNTIF(CO8:CQ8,"M2")=0,COUNTIF(CO8:CQ8,"M1")=0,COUNTIF(CO8:CQ8,"1")=0),"TRUE",IF(AND(COUNTA(CO8:CQ8)>COUNTIF(CO8:CQ8,"A"),COUNTIF(CO8:CQ8,"A")>0),"ERROR","FALSE")))))

Thank you!

**shg** · 03-28-2013, 10:56 AM

Perhaps you could share what the formula is supposed to do?

**missxmelon** · 03-28-2013, 11:06 AM

I'm looking at four columns on a row-by-row basis. The value of any cell in these columns is one of the following: 1, M1, M2, A, EMPTY.

If one row has...
- All EMPTY's, All 1's, a combination of 1's and EMPTY, all A's, a combination of A's and EMPTY --> the formula should return TRUE
- A combination of A's and 1, M1, and/or M2 --> the formula should return ERROR
- All other combinations --> the formula should return FALSE

**missxmelon** · 03-28-2013, 11:20 AM

I simplified my formula just a tiny bit...

=IF(COUNTA(CP9:CS9)=OR(SUM(CP9:CS9),COUNTIF(CP9:CS9,”A”)),"TRUE",IF(AND(COUNTA(CP9:CS9)>SUM(CP9:CS9)>0,COUNTIF(CP9:CS9,"EMPTY")>0,COUNTIF(CP9:CS9,"M2")=0,COUNTIF(CP9:CS9,"M1")=0,COUNTIF(CP9:CS9,"A")=0),"TRUE",IF(AND(COUNTA(CP9:CS9)>COUNTIF(CP9:CS9,"A"),COUNTIF(CP9:CS9,"A")>0,COUNTIF(CP9:CS9,"EMPTY")>0,COUNTIF(CP9:CS9,"M2")=0,COUNTIF(CP9:CS9,"M1")=0,COUNTIF(CP9:CS9,"1")=0),"TRUE",IF(AND(COUNTA(CP9:CS9)>COUNTIF(CP9:CS9,"A"),COUNTIF(CP9:CS9,"A")>0),"ERROR","FALSE"))))

**shg** · 03-28-2013, 01:05 PM

Maybe ...

=LOOKUP(SUMPRODUCT((COUNTIF(A3:D3, {"A","Empty",1,"M1","M2"})> 0) * {16,8,4,2,1}),
{0,FALSE;4,TRUE;5,FALSE;8,TRUE;9,FALSE;12,TRUE;13,FALSE;16,TRUE;17,"Error";24,TRUE;25,FALSE})

**missxmelon** · 03-28-2013, 02:19 PM

Almost perfect! I just need to tweak the formula so it returns ERROR for a combination of A and 1, M1, and/or M2. I tested it out on a row with a mix of A, M1, and Empty and it returned FALSE instead.

**missxmelon** · 03-28-2013, 02:35 PM

Nevermind - I got it to work by changing 25,FALSE to 25,"ERROR"

Thank you SO much for this!

**missxmelon** · 04-23-2013, 04:24 PM

Hi again! Sorry for the late follow-up, but I have a quick question: if I insert more columns (i.e. the criteria expands from four columns to ten columns), will the formula still work? Or does it depend on four columns?