Count a text field and sum by row 1

[beth4530]

How would I get a count of the the med names for each row?

med_name1 med_name2 med_name3 med_name4
Buspirone
Tenex
vyvanse
vyvanse
Buspirone cyclobenzaprine
vyvanse
Carbatrol Cymbalta Invega
Lamictal Risperdal Clonidine
risperidone
Vyvanse

 

[lbass]

I think you need to clarify this. Unless the four fields you are showing can be null, the result would always be 4 per row. Or do you really mean you are looking for counts per some group where the same value could appear in different columns?

-LB

 

[beth4530]

Each row can have up to 10 names...medname1, medname2, etc.
I need to count the names per row.
I've tried exporting to Excel and using the COUNTA function but there are spaces so its counting all ten even if there is only two names listed.

 

[lbass]

Create formulas like this, one per possible medname:

if isnull({table.medname1}) or
trim({table.medname1}) = "" then
0 else 1

Then create a final formula like this:

sum([{@medname1},{@medname2},...{@medname10}])

This will not tell you the number of distinct names, only the total number of names per row.

-LB