COUNT cells in MS Excel

Here I have described all the types of COUNT formulas in MS Excel.......

COUNT(range):

    This function is used to count the number of cells in the given range which contain numbers in it.

COUNT(A1:A10) = 5

COUNTA(range):

    This function is used to count the number of cells in the given range that are not empty.

COUNTA(A1:A10) = 8

COUNTBLANK(range):

    This function is used to count the number of cells in the given range that are empty.

COUNTBLANK(A1:A10) = 2

COUNTIF(range, criteria):

    This function is used to count the number of cells in the given range that match the given condition or criteria.

1)

COUNTIF(A1:A10,"") = 2

 2)

COUNTIF(A1:A10,"=D") = 1

 3)

COUNTIF(A1:A10,"<>D") = 9

 4)

COUNTIF(A1:A10,"<3") = 2

COUNTIFS(range1, criteria1, range2, criteria2,.....):

    This function is used to count the number of cells in the given ranges that match the given conditions.

Note: In all the given ranges & conditions, the minimum count will be taken.....

COUNTIFS(A1:A5, "<3", A6:A10, "<>") = minimum(2,4) = 2

Extras:

"" - blank

"<>" - not equal to blank

" " - contains 'single space'

Thanks for visiting.......!

If u r in need of any projects, you can contact me via gopi21uk@gmail.com
Areas: DOT NET, SQL, HTML, Data Entry, Excel, etc

VBA module (Macro or Function) for MS Excel to display numbers in words

Here is the simple VBA code for MS Excel to convert numbers into words....

e.g.:   ‘23’ -----> “TWO THREE”     &    ‘140’ ----->  “ONE FOUR ZERO”

Steps to add VBA module in MS Excel:

1) Open Excel.

2) Press Alt + F11 to get a window to insert VBA code.

3) Press Insert menu + Module or Right Click on Module tab + Insert + Module to insert new module.

4) Copy and Paste the following code and save it.

Public Function WORD(ByVal num As Double) As String

Dim digits()

digits() = Array("ZERO", "ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX", "SEVEN", "EIGHT", "NINE")

If num <> 0 Then

Do While Val(num) <> 0

WORD = digits(Right(num, 1)) & " " & WORD

num = (num - Right(num, 1)) / 10

Loop

Else

WORD = "ZERO"

End If 

End Function

5) Now Use the function we defined in our module in ur Excel as shown below:

6) Save it in as .xlsm (Excel Macro-Enabled Workbook) file.

Note: When you open the Excel file again, the function will work only if macro is enabled.

By default, when you open MS Excel, macro might not be enabled. It will show warning message that macro has been disabled, when you open it.

You have to enable the macro every time by clicking Options wherever the system you open it as shown below:

Thanks for visiting.......!

If u r in need of any projects, you can contact me via gopi21uk@gmail.com
Areas: DOT NET, SQL, HTML, Data Entry, Excel, etc