Excel: COUNT Function not the Same in Lotus

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
• Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0

SYMPTOMS

When exporting a Microsoft Excel spreadsheet into Lotus 1-2-3, cells that contain the COUNT() function may not be calculated the same in Lotus. The problem occurs when the COUNT() function refers to values that cannot be translated into numbers, such as blanks, letters, or error values.

CAUSE

Lotus does not have a function equivalent to the COUNT() function in Microsoft Excel.

WORKAROUND

If you need a counting function in Microsoft Excel that will always be calculated the same way in Lotus, use the COUNTA function in Microsoft Excel. Also, be sure to always specify a range for an argument that is just one cell. For example, use COUNTA(A1:A1) instead of COUNTA(A1).

MORE INFORMATION

In Microsoft Excel, the COUNT() function counts the number of numbers that are in the list of arguments. Values that cannot be translated into numbers, such as empty cells and cells that contain letters or errors, are ignored.

However, the Lotus COUNT() function counts nonblank cells that are in the list of arguments, the equivalent of the Microsoft Excel COUNTA function. In fact, when you import a Lotus spreadsheet into Microsoft Excel, Microsoft Excel replaces all the COUNT() functions with COUNTA.

The COUNTA() function in Microsoft Excel is equivalent to Lotus' COUNT() function with one exception. If an argument encompasses a blank cell, Lotus' COUNT function will count it unless the argument is given as a range. For example, if A1 is blank, @COUNT(A1)=1 but @COUNT(A1..A1)=0. The Microsoft Excel COUNTA function will return zero in both cases.

Lotus 1-2-3 is manufactured by Lotus Development Corp., a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.

REFERENCES

"Function Reference," version 4.0, pages 68-70