With
A1: (the word to count....eg Sugar)

If you only want  to the cells if the word is the only contents of the cell,
D1: Sugar
D2: Sugar and Spice.
D3: Sugar

try something like this:
A2: =COUNTIF(D1:D10,A1)

Count of Sugar is 2.
----------------------

OR...if the word will occur only once, but may be embeded in other text
like this....
D1: Sugar
D2: Sugar and Spice.
D3: Sugar

try something like this:
A2: =COUNTIF(D1:D10,"*"&A1&"*")

Count of Sugar is 3.
----------------------

BUT...If the word may be embedded in other text, 
and possibly repeated
like this....
D1: Sugar and Spice
D2: Sugar Bears contain no Sugar.
D3: Spice

Then this formula counts each occurrence:
A2: =SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)

Count of Sugar is 3.
Note: SUBSTITUTE is case sensitive, hence the UPPER function.