Here is the formula. It refers to a list of names:

Name List

John Smith
Mary Smith
Lou Philips
Jenny Mao
Philip Long

=IF(COUNTA(B1:B6)=1,B1,IF(COUNTA(B1:B6)=2,CONCATENATE(B1," and ",B2),IF(COUNTA(B1:B6)=3,CONCATENATE(B1,", ",B2,","," and ",B3),IF(COUNTA(B1:B6)=4,CONCATENATE(B1,", ",B2,", ",B3,","," and ",B4),IF(COUNTA(B1:B6)=5,CONCATENATE(B1,", ",B2,", ",B3,", ",B4,","," and ",B5),IF(COUNTA(B1:B6)=6,CONCATENATE(B1,", ",B2,", ",B3,", ",B4,", ",B5,","," and ",B6)," "))))))

Basically, I have allotted for up to 6 names to be concatenated with commas and "and" on output. I need to find a way to allow for an unlimited amount of concatenated names. Output example would be as follows:

John Smith, Mary Smith, Lou Philips, Jenny Mao, and Philip Long

Please let me know if you need more data.

Thank you in advance for your help!

Shannon