Nested IF statement for range of cells containing different combinations of text

[Suelizzy]

Hello and thanks in advance for your help.

I'm attempting to summarize the output from a poorly designed online survey that contains 170 columns (really).

Is there a nested IF statement that will output into one of four phrases: "Safe", "At Risk", "Both", or "Not Indicated"?

One of several safety topics are listed in column T, and a mix of blank cells, "Safe" and/or "At Risk" are present in any of the cells in columns U through FK.

There are over 6500 rows...

Here's how I'd like to summarize each row:

If there are one to several cells within the range of columns U through FK that contain (only) the text "Safe", then return "Safe"
If there are one to several cells within the range of columns U through FK that contain (only) the text "At Risk", then return "At Risk"
If there is any mix of cells within the range of columns U through FK that contain "Safe" AND "At Risk", then return "Both"
If there is no text in the cells within the range of columns U through FK , then return "Not Indicated"

Any help is appreciated!

Thanks mucho!

 

[pjmorris]

Try this in cell T3 (if that is the top cell in Column T):

=IF(AND(SUMPRODUCT(--(UPPER(U3:FK6500)="SAFE"))>0,SUMPRODUCT(--(UPPER(U3:FK6500)="AT RISK"))>0),"Both",IF(SUMPRODUCT(--(UPPER(U3:FK6500)="SAFE"))>0,"Safe",IF(SUMPRODUCT(--(UPPER(U3:FK6500)="AT RISK"))>0,"At Risk","Not Indicated")))

then copy down.

I've used SUMPRODUCT rather than COUNTIF because it allows me to convert the tested cells to Upper Case and hence avoid any case sensitivity.

PS. The equation is quite slow so if you're confident that the case of the search is known, ie all Safe enteries are 'Safe' not SAFE or safe, then you could shorten tht function by deleting the UPPER(U3:FK6500) and replacing with U3:FK6500 as this should speed it up.

PPS. Try this first as the function above is so slow the spreadsheet looks like its crashed!!!

=IF(AND(SUMPRODUCT(--(U3:FK6500="SAFE"))>0,SUMPRODUCT(--(U3:FK6500="AT RISK"))>0),"Both",IF(SUMPRODUCT(--(U3:FK6500="SAFE"))>0,"Safe",IF(SUMPRODUCT(--(U3:FK6500="AT RISK"))>0,"At Risk","Not Indicated")))

HTH

 

[MarcelBeug]

Another solution, e.g. in T2:

=INDEX({"Not indicated","Safe","At Risk","Both"},1+ISNUMBER(MATCH("Safe",U2:FK2,0))+2*ISNUMBER(MATCH("At Risk",U2:FK2,0)))

 

[pjmorris]

Please ignore the previous post - it runs unbelievably slowly because I've used the wrong range!!

This formula actually works in T3 and copied down:

=IF(AND(SUMPRODUCT(--(U3:FK3="SAFE"))>0,SUMPRODUCT(--(U3:FK3="AT RISK"))>0),"Both",IF(SUMPRODUCT(--(U3:FK3="SAFE"))>0,"Safe",IF(SUMPRODUCT(--(U3:FK3="AT RISK"))>0,"At Risk","Not Indicated")))

HTH

 

[pjmorris]

@MarcelBeug,

I like that solution, its elegant and I think quicker than mine.

Thanks for sharing.

 

[MarcelBeug]

Thanks Peter.

 

[Suelizzy]

Hello and thanks again for your help with this. I was wondering if there is an equivalent type of query in access that will output the same information - - without having to use each of the 100+ field names. I've searched the web on how to create a named range in Access but not sure I'm searching for the right thing or if I'm even on the right track.

Basically, I would like to summarize data from a bunch of columns into one field in access depending on the combination of data in the bunch of columns. Not sure concatenate is the right thing... If anyone knows if this can be done, and if so, how to do it, I will love you forever! Thanks :+)