Why doesn't Excel like the word hot?

Microsoft Office excel 2007 home & stude...
July 12, 2010 at 12:52:02
Specs: Windows XP
Help!! Can anyone sort this out? I have a column with types of engines starts, hot or cold, and I want to count the total number of occurances for each type. I am using the COUNTIF function and it works for "cold" but will not pick up "hot". Any ideas would be much apprecited.

See More: Why doesnt Excel like the word hot?

Report •

July 12, 2010 at 13:06:28
Since I can't see your spreadsheet from where I'm sitting, I'll guess that either the cells don't contain the word hot or that your formula isn't looking for the word hot.

Try this:

Find a cell in the range that you are trying to count that looks like it contains hot. Let's say it's A5.

In another cell, say B1, enter this:


If it doesn't return TRUE, then A5 doesn't contain hot.

Maybe there's an extra space or a non-printing character or something like that going on.

Try the CLEAN and/or TRIM function on the cells to see if that helps.

Report •

July 12, 2010 at 13:12:59

There is no reason why it shouldn't work.

Can you post the formulas you are using (for both hot and cold starts)

If your COUNTIF() formula uses a reference to "hot" or "cold" please include the data in the cell or cells referenced.

When you say will not pick up, what result do you get from the COUNTIF() function.


Report •

July 12, 2010 at 13:27:10
Ok. The column with the hot/cold starts in it is populated from a drop down list containing, unsuprisingly, hot and cold. The functions I am using are =COUNTIF(A2:A7,"hot") and =COUNTIF(A2:A7,"cold"). The cold one works fine but the result in the hot is always 0. I have tried it without the drop down lists and it still won't work and I tried another three letter word (off) and that does work. I've tried it on Excel 2007 and 2003 and they are both the same. If anyone can think of another word for hot, I'm open to suggestions!!

Report •

Related Solutions

July 12, 2010 at 13:35:13
Ok, this is freaky! Just to experiment, I changed hot to HOT and it worked! I changed it back again and it now works!!! Maybe I had an extra character in there as DerbyDad suggested.

Report •

Ask Question