Solved How to apply multiple formulas that will show a single value

October 24, 2018 at 14:04:58
Specs: Windows 7
 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.

See More: How to apply multiple formulas that will show a single value

#1
October 24, 2018 at 16:13:26
 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?message edited by DerbyDad03

Report •

#2
October 26, 2018 at 07:13:12
 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/OCT0: X/X/A/X/Terminated/Terminated/Terminated = value in 1st column should be zeroX= Attended MeetingA= Absentmessage edited by Rhinosmom13

Report •

#3
October 26, 2018 at 07:33:26
✔ Best Answer
 Were you able to fix the formula on your own? It's just a matter of swapping the value_if_true arguments for the IF functions. I done that. Notice the bolded values:=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,""))

Report •

Related Solutions

#4
October 26, 2018 at 09:34:28
 This worked perfectly... you are awesome!!! Thank you, thank you, thank you!!!!!!!1

Report •

#5
October 26, 2018 at 09:42:13
 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 answer questions in your spare time as some of us do. ;-)

Report •