**To round a number to millions with the thousands shown as a decimal:**

1. Press Ctrl+1 to open the Format Cells dialog box.

2. Select the Number tab, and from Category, select Custom.

3. In the Type box, enter the following Custom Formatting syntax:

#,##0.0,, ;[Red](#,##0.0,,);- ;

Result:

• Original number: 5,645,625

• The displaying formatted number: 5.6

**Note:**

The number formatting syntax is:

Positive; Negative; 0; Text

**Screenshot // Rounding Numbers to Millions with Thousands as a Decimal**

I have two sales reports. Both have the same format and layout. I am trying to generate a list that compares one report to the other and picks out the records that do not appear in the list i.e. this years sales vs last years sales : pull out all records that appeared last year but are not in this years.

“Hello,

Is there an easier way to count the amount of text cells in a column automatically via a formula.

I understand I can put a 1 in a column next to each text cell in the previous column, then formulate to count the 1′s, but I think thats extra work.

Isnt there a easier way to just add the amount of cells in a column that show text, as opposed to numbers, at the end of the column?

I dont/cant figure out if the COUNT function is able to do this or not, it appears to only work with numbers.

Any advice/assistance would be appreciated. Thank you very much in advance.”

“I’m trying to use a “”countif”” function in a 3 dimensional environment on a workbook that has pages representing months of the year.

I would like Excel to count all of the entries in Cell A1 on all of the pages, if there is anything there in any of them.

On a totals page, in Cell A1, I tried the following formula:

countif(Jan:Dec!A1,””>=a””)

It gives me a #Value error.

If I use:

countif(Dec!A1,””>=a””)

it works fine. So there must be something in the way I am defining the January to December worksheet pages that is messing up the formula, but I can’t figure it out. Can you help me? “

Hi Ron. My only idea is the formula =LEN(text) which counts the characters for every cell. Maybe you have to add each cell. For example =(LEN(text)+LEN(TEXT)..this is just a one time set-up.

Hope this can help you.

0.0,, Works just as well

0.0 says that it must have 1 decimal

,, means that it must be divided by 1000 and another 1000 (each “,” means divide by a thousand)

If you want to place text afterwards as Unit of measure use this:

0.0,, “MM”

This way is clearly noted that you are talking about Millions