Formula is too long

Dave,

What are you trying to do? A multiconditional count?

Aladin

A little confused, however if you are trying to see if a particular phrase appears in the range A2:A250 I suggest using countif. I tried the way you have it and it did not seem to work if A3 was incorrect.

Try this instead:
=(countif(A2:C250,"phrase")>0)
This will return a True if it appears in the range and a false if it does not. If you want to know if they all match, change the >0 to =747
TRUE is the same as 1 and FALSE is the same as 0. This will remove alot of the long if statements. What I don't get is that if any of these statements don't appear you will get a zero out because you are multiplying together. Anyway this would replace your formaula as it stands.

AND(countif(A2:C250,"phrase1")<0,countif(A2:C250,"phrase2")<0,.......) you can replace the dots with as many counif statements as required. This will give a 1 if all phrases appear and a 0 if any don't.

Hope this helps, if I misunderstood what you are looking for it should give you some ideas...

Rob.

Yes, Aladin. You were the one that helped me from the beginning (you got me this far), but I deleted your email by mistake, so I couldn't ask for your help directly.

I have the 3 software baselines that are listed on a Software Problem Report (SPR) and each baseline can have a status of:

Retest,
Assign for Analysis,
Working,
In Test,
In CM,
Ready for Release,
Waiting for Govt,
Closed, or
On Hold.

These are listed in priority order, that is... if one of the baselines has a Retest, then the SPR will be counted as a Retest, regardless of the status of the other two. If one baseline has a status of Working, another has In Test, and another has In CM, then that SPR will be counted as Working. In other words, the SPR will be counted as the "highest on the list" of statuses. Of course, I also need it to be counted only once, that is, if a single SPR has each baseline with a status of Working, it should only count for 1, because it is only counting the "highest priority status" of that individual SPR.

I hope I have explained it well enough. I know it is difficult, but if you can send me an email, maybe I can send you my db, so you can see.

Sorry, if I've confused you.

Dave

Dave,

The error is due to the fact that the "string length" of the formula exceeds the limit of 255 (i.e., 255 chars) by adding more terms to cover additional categories. Anyway, it's not a case of too many nested IFs. As a solution, change each of the used ranges ('Imported Records'!A2:A250, 'Imported Records'!B2:B250, and 'Imported Records'!C2:C250) into named dynamic ranges where you opt for short names, e.g., BLINE1, BLINE2, and BLINE3. Substituting these names in the array formula that you use will give you the room you need.

PS. I adjusted your workbook along the line described above & it's underway to you. By the way, the adjustments will somewhat slow down the system.

Aladin

============= Yes, Aladin. You were the one that helped me from the beginning (you got me this far), but I deleted your email by mistake, so I couldn't ask for your help directly. I have the 3 software baselines that are listed on a Software Problem Report (SPR) and each baseline can have a status of: Retest,

> What I don't get is that if any of these statements don't appear you will get a zero out because you are multiplying together.

The following 2 formulas can perhaps clarify why multiplications occur in the array formula that Dave posted (he forgat to mention it is an array formula).

1. {=SUM(IF(BLINE1="Retest",1,IF(BLINE2="Retest",1,IF(BLINE3="Retest",1,0))))}

where BLINE1 stands for the range in column A, BLINE2 for the range in column B, etc. Moreover, each row consisting of columns A, B, and C is considered to be an SPR.

The above formula counts each row as a "Retest" case (or SPR) if any of the cells in that row contains the "Retest" phrase.

2. {=SUM(IF(BLINE1="Assign for Analysis",1,IF(BLINE2="Assign for Analysis",1,IF(BLINE3="Assign for Analysis",1,0)))*IF(BLINE1="Retest",0,IF(BLINE2="Retest",0,IF(BLINE3="Retest",0,1))))}

This formula counts each row as an "Assign for Analysis" case if any of the cells in that row contains the "Assign for Analysis" phrase, but not the "Retest" phrase. For example,

A3="Retest",B3=blank,C3="Assign for Analysis"

should NOT be counted as a case of "Assign for Analysis".

It's this re-categorization that makes the multiplicative terms necessary.

Note. The formula could be shortened considerably if some phrases are guaranteed NOT to occur together in an SPR row.

Aladin