I have 6 columns that I would like to reference.

If any of the first 3 and the last are basically greater then 0, then I

would like to put a code in an additional column.

If any of the first 3 are greater then 0 and there is nothing in any of the

last 3 columns, then I would like a different code

If any of the last 3 are greater then 0 and there is nothing in the first 3

columns then I would like a different code in the additional column.

Is there a way to go about doing this?

We're a school. We have start dates, grad dates and licensing dates for two

different programs. Based on the answers to these columns, the record would

be coded.

This is windows XP

Excel 2003

Thanks

one way

=IF(AND(OR(A1>0,B1>0,C1>0),F1>0),"first",IF(AND(OR(A1>0,B1>0,C1>0),AND(D1="",E1="",F1="")),"different",IF(AND(OR(D1>0,E1>0,F1>0),AND(A1="",B1="",C1="")),"differentagain","")))

enter this in G1

assumption........... your first condition

If any of the first 3 and the last

I understand that to be if the last and any of the first three..............

If not correct you may now be able to change this to suit.

--

Greetings from New Zealand

=IF(AND(OR(A1>0,B1>0,C1>0),F1>0),"first",IF(AND(OR(A1>0,B1>0,C1>0),AND(D1="",E1="",F1="")),"different",IF(AND(OR(D1>0,E1>0,F1>0),AND(A1="",B1="",C1="")),"differentagain","")))

enter this in G1

assumption........... your first condition

If any of the first 3 and the last

I understand that to be if the last and any of the first three..............

If not correct you may now be able to change this to suit.

--

Greetings from New Zealand

I tested this previous suggestion and it seemed to fail for the 2nd condition

if 2 is in A1 and 1 is in D1.

Try this array formula:

=IF(AND(AND(D1:F1=""),OR(A1:C1>0)),"Code2",IF(AND(OR(D1:F1>0),AND(A1:C1="")),"Code3",IF(OR(A1:C1>0,F1>0),"Code","")))

array - you must press Shift+Ctrl+Enter to enter it.

--

If this helps, please click the Yes button.

Cheers,

Shane Devenshire

It is unclear whether you want to evaluate this on a row-by-row basis, or

whether you want to evaluate the conditions against entire sets of columns.

On a row-by-row basis:

=if(countif(A1:C1,">0")>0,

if(countif(D1:F1,">0")>0,"code1","code2"),

if(countif(D1:F1,">0")>0,"code3","code4"))

Based on entire sets of columns:

=if(countif(A1:C100,">0")>0,

if(countif(D1:F100,">0")>0,"code1","code2"),

if(countif(D1:F100,">0")>0,"code3","code4"))

Some notes:

1. The second formula (entire sets of columns) assumes that either all of

the columns have the same number of rows of data or at least that shorter

columns of data are blank in the remaining rows up to the longest column.

2. The COUNTIF usage does not distinguish between cells with nothing and

cells with zero or negative values (instead of dates). Some combination of

COUNTA and COUNTIF could make that distinction. But it seems unnecessary

since you do not specify any codes to cover it.

3. My "code1", "code2" and "code3" are intended to correspond to the order

of the conditions that you specified, namely:

a. "any of the first 3 and the last are basically greater then 0"

(code1);

b. "any of the first 3 are greater then 0 and there is nothing in any of

the last 3 columns" (code2);

c. "any of the last 3 are greater then 0 and there is nothing in the

first 3 columns" (code3).

4. I added "code4" to cover a condition that you do not specify, namely:

nothing in the first __and__ last set of 3 columns.

If you are confident that the condition cannot arise, you could simply

remove ``,"code4"`` or replace it with ``,"ERROR"``.

----- original message -----

asically, if any of the first 3 columns..(B, C or D) have anything in them,

then i want Column E to have the following code CEM

If they don't have anything then it can be just CE

If columns F, G, or H have anyting in them then it should add an A to either

CE or CEM

dpending on what is any of these columns it could be CE, CEA CEM or CEMA

"JoeU2004" < XXXX@XXXXX.COM > wrote in message

news:eEfJ2Dp$ XXXX@XXXXX.COM ...

then i want Column E to have the following code CEM

If they don't have anything then it can be just CE

If columns F, G, or H have anyting in them then it should add an A to either

CE or CEM

dpending on what is any of these columns it could be CE, CEA CEM or CEMA

"JoeU2004" < XXXX@XXXXX.COM > wrote in message

news:eEfJ2Dp$ XXXX@XXXXX.COM ...

********Meg" <alumni(removethis)@swedishinstitute.edu> wrote:

I think you mean row-by-row. That is the only way that "column E" makes

sense to me.

I think the following meets your needs. Put the following formula into the

E1 (i.e. the first row of data) and copy down:

="CE" &

if(counta(B1:D1)>0,"M","") &

if(counta(F1:H1)>0,"A","")

Note that I switched from using COUNTIF to COUNTA because now you are saying

"have anything" instead of "greater than zero". If you prefer:

="CE" &

if(countif(B1:D1,">0")>0,"M","") &

if(countif(F1:H1,">0")>0,"A","")

----- original message -----

"********Meg" <alumni(removethis)@swedishinstitute.edu> wrote in message

news:uFI%23Uax$ XXXX@XXXXX.COM ...

I think you mean row-by-row. That is the only way that "column E" makes

sense to me.

I think the following meets your needs. Put the following formula into the

E1 (i.e. the first row of data) and copy down:

="CE" &

if(counta(B1:D1)>0,"M","") &

if(counta(F1:H1)>0,"A","")

Note that I switched from using COUNTIF to COUNTA because now you are saying

"have anything" instead of "greater than zero". If you prefer:

="CE" &

if(countif(B1:D1,">0")>0,"M","") &

if(countif(F1:H1,">0")>0,"A","")

----- original message -----

"********Meg" <alumni(removethis)@swedishinstitute.edu> wrote in message

news:uFI%23Uax$ XXXX@XXXXX.COM ...

1. Need help sorting out ANDs and ORs in parameter query

2. Specify list items with unlimited ANDs and ORs?

3. IF's & AND's

4. Nesting IF's and AND's - a bit of help please...

5. two questions: and's inside if's and checking the return code of a program

6. Proper way to write a conditional statement with ands

7. if & ands and either indirect or offset - need help fast pleas

8. SUMPRODUCT with AND's and OR's?

12. Using embeded AND's in WHERE clause?

13. Problem with calculations and multiple AND's in criteria

14. how to combine and's with or's in a if statement

15. Stenciling and multiple buffer "ands"

6 post • Page:**1** of **1**