Computer functions/spreadsheet issues

[WeatherwaxOn]

Not sure if this is really the right forum but I figured it would be likely someone might know.
I have a spreadsheet with multiple rows and columns and was using the COUNTIF formula to tot up how many instances of a word is in a particular column.
So set up =COUNTIF(start cell:end cell, "specific value")
For some reason this just will not work on one particular word. Or rather, the function will not work in the cell. I've tried copying the formula from the cell above (which is searching a different word), but as soon as I change to what I am looking for it just shows 0.
Any ideas?

It depends, COUNTIF will only work if the cell contains the word and only the word, it is not case sensitive, but a space before or after the word, or other words in there as well mean it does not count those cells, it is a whole cell value formula. The most common issue is a following space.

I would say the cell you think matches doesn't actually match 0 like pp said, there could be a space after the word. try overtyping the cell you think that matches to see if it counts it then

I'm having trouble posting screenshots for some reason but I've removed all the formula and text and retyped it and still no joy.
Is there such a thing as a word that excel just hates?!

@WeatherwaxOn

I'm having trouble posting screenshots for some reason but I've removed all the formula and text and retyped it and still no joy. Is there such a thing as a word that excel just hates?!

Only if that word is a specific function in Excel and even then it should be fine with it so long as the formula is follows the correct syntax.

I'm trying to get it to count only cells containing a particular word from a column with various words in.
E.g. column is a list of veg; carrot, onion, celery, potato, broccoli. I want to count up broccoli.
Column is G3:G407 so my formula is =COUNTIF(G3:G410, "broccoli").
The cell with this formula is in a table which also counts carrot, onion, celery etc. individually. Those formulae all work but broccoli doesn't.
I've tried copying the cell above and changing the search word. I've tried deleting the formula and typing it again.
It still doesn't work.

Grrr, I hate it when this happens!
Have you checked the format of the cell with the first instance of "broccoli"?
Check it's set to 'General" as sometimes that's the issue. Also check there is no conditional formatting set over the sheet.

Also copy the word broccoli and paste it into the formula using ctrl C and V.

But yes cell formatting can upset it too.

My bet is that there's a space after the word in the cells you want to count.

A couple of thoughts:

1. Have you used the range G3:G407 / G3:G410 in your formula for one vegetable and then copied it into other cells?
   It may have changed the relative range and your broccoli isn’t being counted as the cell is no longer in the range
   If so change the formula to:
   =countif($g$3:$g$407)

2. Try it without the word in the formula.
   Eg List each word to be counted in F then use the cell reference in the countif formula:

=countif($g$3:$g$407,f410)

Ps spot my non-deliberate mistake with the onion count

@MrsFin

My bet is that there's a space after the word in the cells you want to count.

MrsFin thakyou yes, I have repeated my checks one more time, deleted all the information from the cells, retyped the word and then deleted anything after the last letter. This time it worked.

Thanks everyone for your help.

Ah the random space strikes again!!

HTH!

It's always a space