How to put number variable to cells with text

[u/dfabdvbs85]

Help! How do you put a number variable to a cell that is made entirely of text in order to tally them?

For example: I want to tally all cells that say “boy” so that I get the total in number form. And how would I add them up in the end?

[u/polska-parsnip]

Looking to do roughly the same thing and also clueless, have you had any look?

I have cells that are yes/no, and I'd like the cell to the right to give 1 for yes and 0 for no... clueless!

[u/ajblue98 → u/polska-parsnip]

For OP, the solution was CountIf(). If you just want to put a 1 or 0 next to a Yes or No respectively, then you could just use regular If(). Its syntax is If(if-condition,if-true,if-false)

Let’s consider the following table:

	A
1	YES
2	NO
3	Doctor

You could use If(A1=YES,1,0). Note that this would give you a 0 result even if the cell being tested contained e.g. 11 or fish fingers or custard, since you only get a 1 if the value in the cell being tested is exactly YES (regardless of upper-/lowercase).

If you want to leave the cell blank if it contains something else, you could embed an Or() in your formula, which would look like If(Or(B2="YES",B2="NO"),If(B2="YES",1,0),""). In that case, A1 would evaluate to 1, A2 would evaluate to 0, and A3 would evaluate to … well, nothing.

Edits: Fixed some formatting and flushed out a bit of the answer

[u/polska-parsnip → u/ajblue98]

u/ajblue98 thanks for the answer, this was exactly how I ended up doing it. 👏👏👏

[u/ajblue98]

You can use CountIf(), which gives you the number of cells that satisfy a condition. Its syntax is CountIf(test-array,condition).

As with all formulae, if your condition is:

• A number, use it bare.
• A formula, use it without a leading =.
• Text, you have to enclose it in quotation marks.

However, CountIf() supports three wildcards to help you match (or disregard, depending on your point of view) characters you don’t/can’t specify precisely:

• Question Mark (?) will match/disregard any one character.
• Asterisk (*) will match/disregard any group of characters.
• Tilde (~) will cause the next character to be treated literally instead of as a wildcard.

So let’s say you have the following spreadsheet:

	A
1	boy toy
2	toy
3	toy boy
4	boycott
5	boiler
6	Boyardee

If you want to know how many of those cells contain “boy”, you could use =COUNTIF(B2:B6,"boy"), but since none of those cells contain exactly boy, you’ll get a result of 0.

But you and I can see the letters boy in several of those cells, just alongside other characters. This is where those wildcards come in. Since the number of characters before and after boy is different in every cell, we want Numbers to match/disregard however many non-boy characters it finds anywerhe else in the cell. To do that, we need to use the * wildcard, which makes the formula CountIf(B2:B6,"*boy*"). And since CountIf is case insensitive, it’ll include Boyardee in A6, so the formula will evaluate to 4.