Can I make Yes = 1?

Ask for Help
1

Hi everybody!

I’m having an issues. I have columns that have Yes, No, & Blanks. I need to get a count of the yes’s so I’d like to have yes = 1. How do I convert the yes’s to 1’s, or count them in another or the same column so that I could then add them? And/or is there a better way to accomplish the same thing?

Thanks!

Shared with CloudApp

2

Hi Chris!

You can use len() and lookupRecords to count how many records have a value of yes, essentially making yes = 1.

I created a short example here: Community #1677

We have two columns in Table1 that contain yes values. You can see the count in the summary table at the top of the page.

The formula in the ‘Col A YES’ column of the summary table is:

len(Table1.lookupRecords(Col_A="yes"))

First, we use lookupRecords to find all records in Table1 where the value in Col A is yes. Then, we use len() to count the number of records found.

image
image1402×772 58.8 KB

Let me know if you have any other questions!

Thanks,
Natalie

1 Like
3

Thanks Natalie, that was very helpful! How would is it possible to accomplish something similar where instead of the row count, it would be a helper column where the 1 would appear next to each Yes? For example in a Col C?

4

Hi @Chris_Scott1, I made a small variant of @natalie-grist’s document at Community #1677 - Grist adding a Col C with the formula:

1 if $Col_A.lower() == 'yes' else None

Something along those lines might be what you want. I added in a lower to use lowercase since in Natalie’s doc yes is used and in your doc Yes is used.

1 Like
5

Thanks Paul. This was helpful also! I actually needed both of the suggestions. Thanks again.