Excel 2007 : Combine several IFs statements/keywords in a single cell

Hi Experts,

1. I have 6 columns. Column A,B,C needs to be filled up based on the keywords in Column D,E,F respectively.

2. Need your help to combine several search for a keyword in a cell to return a common value.
For example, keywords such as "Hardware"."Harddisk", "RAM", "scanner" in column F are to return the common value "Break-Fix" in Column C.

3. I have created several IF statements to cater to the number of possible keywords in column C for the respective main headers.
Below are the examples

The function below will Display Toner Replacement under Tier 3
=IF(SUM(COUNTIF(Resolution Cell,{"*fuser*","*toner*","*drum*","*cartridge*","*mark*","*dirty*","*smudge*"}))>0,"Toner Replacement","Others")

The function below will Display Maintenance Kit under Tier 3
=IF(SUM(COUNTIF(Resolution Cell,{"*maintenance kit*","*kit*"}))>0,"Maintenance Kit","Others")

The function below will display Re-image under Tier 3
=IF(SUM(COUNTIF(Resolution Cell,{"*repair*","*restor*","*clone*","*imag*","*Reimag*"}))>0,"Re-Image","Others")

The function below will display Mapping under Tier 3
=IF(SUM(COUNTIF(Resolution Cell,{"*map*"}))>0,"Mapping","Others")

The function below will display Access Rights under Tier 3
=IF(SUM(COUNTIF(Resolution Cell,{"*access*"}))>0,"Access Rights","Others")

The function below will display Paper Jam under Tier 3
=IF(SUM(COUNTIF(Resolution Cell,{"*Paper Jam*","*jam*"}))>0,"Paper Jam","Others")

The function below will display Operating System under Tier 1
=IF(SUM(COUNTIF(Description Cell,{"*window*","*OS*","*vista*","*Operating System*","*boot*","*post*"}))>0,"Operating System","Others")

The function below will Break-Fix under Tier 2
=IF(SUM(COUNTIF(Resolution Cell,{"*replace*","*initialize*","*dock*","*card*","*fan*","*cable*","*cord*","*RAM*","*adapt*","*ups*","*power supply*","*paralle*","*port*","*CD*","*dvd*","*batt*","*Reseat","*hardware*","*vendor*","*mouse*","*faulty*","*LAN*","*monitor*","*screen*","*NIC*","*MB*","*plug*","*hard*","*disk*","*keyboard*","*mother*"}))>0,"Break-Fix","Others")


Will it be possible to combine the IF statements of 1 column into 1 statement?

Hi TMShucks's,

I believe I have no other choice but to do it progressively as per your advise for Column C.
Thank you for your assistance.

Do you happen to know if there is a maximum number of arguments within the IF statements given?

Best regards,
Shad

Originally Posted by TMShucks

Progressively replace "others" with the next formula, removing the equals sign. For example:

=IF(SUM(COUNTIF(F2,{"*fuser*","*toner*","*drum*","*cartridge*","*mark*","*dirty*","*smudge*"}))>0,"Toner Replacement",IF(SUM(COUNTIF(F2,{"*maintenance kit*","*kit*"}))>0,"Maintenance Kit",IF(SUM(COUNTIF(F2,{"*map*"}))>0,"Mapping",IF(SUM(COUNTIF(F2,{"*repair*","*restor*","*clone*","*imag*","*Reimag*"}))>0,"Re-Image","Others"))))


Regards, TMS

Hi TmShucks,

I would consider the is suggestion solved my query.
Thanks a million!