Formula for calculating items in cell

[zchang]

Hello, I am a newbie so please bear with me. I want to calculate the items in a cell range (e.g. A1:E1) that contain comma separated lists but I don't want to count values like N/A or n/a. I would like to input the total in one cell. So, the data would look something like this:

Rick,Sally,Susan N/A David,Randy,Bob n/a Phil 7(total number)

Any suggestions on how I should accomplish this?

Thanks for any help in advance.

 

[Michael M]

Hi and Welcome to the Board
As "A1:E1" is only 5 cells...how do you get 7 as your result ?
If that's a typo, try something like

=COUNTIF(A1:E1,"<>n/a")

On reading this a little closer, can I assume all the names are in one cell.
If so, try using Text to Columns" first to seperate each name into a new cell, and then use the above formulae

 

[pgc01]

Hi

Assuming each cell either has a comma separated list or n/a(N/A), like in the example, try:

=SUMPRODUCT(1+LEN(A1:E1)-LEN(SUBSTITUTE(A1:E1,",","")))-COUNTIF(A1:E1,"n/a")

 

[zchang]

Thanks Micheal M and PGC for the reply. PGC, your formula was exactly what I needed!! Thanks again guys.

 

[pgc01]

You're welcome. Thanks for the feedback.