Hello,

I am trying to give a single number value of "0" to a specific cell (i.e. D1) of a row with a range of 12 cells (i.e. D4:O4) that contains either the word "Terminated" or "Resigned". If it contains either "X", "A", "W", "V", "FMLA", "JD" within those mentioned cells then the value is "1". If someone can help with that I'd really appreciate it.

We may need a little more information. If D4:O4 will always contain at least one of your values, the solution may be very simple. =IF(SUMPRODUCT((D4:O4="Terminated")+(D4:O4="Resigned"))>0,1,0)

This will check the entire range for at least 1 occurrence of one of those 2 terms. If an occurrence exists, the answer will be 1. If not, the result will be 0. It is sort of "assuming" that one of your other desired values is there. i.e. it doesn't actually check for "X", "A", "W", "V", "FMLA" or "JD". It only checks for Terminated and Resigned.

If however, it must also check for "X", "A", "W", "V", "FMLA" or "JD" (i.e. could the entire range be empty or in some other manner not contain any of your 8 values) then the formula gets a little longer.

=IF(SUMPRODUCT((D4:O4="Terminated")+(D4:O4="Resigned"))>0,1,IF(SUMPRODUCT((D4:O4="X")+

(D4:O4="A")+(D4:O4="W")+(D4:O4="V")+(D4:O4="FMLA")+(D4:O4="JD"))>0,0,""))There may be an easier way, but off the top of my head, this seems to work.

There is also another thing that should be asked:

Will there be a situation such as Terminated in one cell and X in another? If so, which value would take priority?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Thank you DerbyDad03! - this did work but the values are reversed.

It's giving me a "1" value for Terminated or Resigned and "0" for the other values.

and no value for blanks which is perfect.And yes Terminated or Resigned would take precedence:

i.e.

[score]APR/MAY/JUN/JUL/AUG/SEP/OCT

0: X/X/A/X/Terminated/Terminated/Terminated = value in 1st column should be zeroX= Attended Meeting

A= Absent

message edited by Rhinosmom13

Were you able to fix the formula on your own? It's just a matter of swapping the value_if_truearguments for the IF functions. I done that. Notice theboldedvalues:=IF(SUMPRODUCT((D4:O4="Terminated")+(D4:O4="Resigned"))>0,

0,IF(SUMPRODUCT((D4:O4="X")+

(D4:O4="A")+(D4:O4="W")+(D4:O4="V")+(D4:O4="FMLA")+(D4:O4="JD"))>0,1,""))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

This worked perfectly... you are awesome!!! Thank you, thank you, thank you!!!!!!!1

While I appreciate the thanks, I hope that you will take the time to figure out how the formula works. You can use the Evaluate Formula feature on the Formulas menu to Single Step through the formula to see how it evaluates the values in the range. Learning how the formula works may help you come up with your own formulas in the future without having to wait for an answer in a forum. Someday, you may even be able to

answerquestions in your spare time as some of us do. ;-)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll