EXCEL: Know about COUNTIF Function

COUNTIF(range, criteria)

Returns the number of non blank cells that satisfy a particular condition.

	range	The range of cells from which you want to count the cells.
	criteria	The logical test that will filter out the data.

REMARKS

	·	The "range" must be a cell range or a named range.
	·	The "range" does not have to be sorted into any order.
	·	The "criteria" can be a cell reference or a named range.
	·	The "criteria" can be in the form of a number, expression, or text.
	·	The "criteria" can be expressed as numerical (i.e. 32) or as a string (i.e. "32").
	·	The "criteria" can use string matching, i.e. *M* is all words that contain the letter "M". This is not case sensitive.
	·	If you are checking for numerical conditions make sure the cells contain numbers and not text.
	·	Example 1 - This counts the number of cells that contain the text "apples".
	·	Example 2 - This counts the number of cells that contain a negative number.
	·	Example 3 - This counts the number of cells that contain a number less than 40.
	·	Example 4 - This counts the number of cells that contain a number greater than 40.
	·	Example 5 - This counts the number of cells that contain non zero values.
	·	Example 6 - This counts the number of cells that contain a value between 1 and 100.
	·	Example 7 - This counts the number of cells that contain text.
	·	Example 8 - This counts the number of cells that contain the letter "B". This is not case sensitive.
	·	Example 9 - This counts the number of cells that contain the letter "b". This is not case sensitive.
	·	Example 10 - This counts the number of cells that contain only three letters.
	·	Example 11 - This counts the number of cells that have the letter "e" as their second character.
	·	Example 12 - This counts the number of cells that contain the text "Y".
	·	Example 13 - This counts the number of cells that contain a date that is less than todays date.
	·	Example 14 - This counts the number of cells that contain a date that is less than 30 days before todays date.
	·	Example 15 - This counts the number of cells that contain either the text "Y" or the text "N".
	·	For examples of how to include multiple conditions, please refer to Array Formulas > Multiple Conditions

EXAMPLES

A B C 1 =COUNTIF(B1:C8,"apples") = 0 20 35 2 =COUNTIF(B1:C8,"<0") = 1 -40 85 3 =COUNTIF(B1:C8,"<40") = 3 60 125 4 =COUNTIF(B1:C8,">40") = 7 180 95 5 =COUNTIF(B1:C8,"<>0") = 16 500 55 6 =COUNTIF(B1:C8,">=1")-COUNTIF(B1:C8,">=100") = 6 Better dot 7 =COUNTIF(B1:C8,"*") = 6 Solutions com 8 =COUNTIF(B1:C8,"*B*") = 1 Y N 9 =COUNTIF(B1:C8,"*b*") = 1 12-Jul-2008 21-Jul-2020 10 =COUNTIF(B1:C8,"???") = 2 11 =COUNTIF(B1:C8,"?e*") = 1 12 =COUNTIF(B1:C8,"Y") = 1 13 =COUNTIF(B9:C9,"<"&TODAY()) = 1 14 =COUNTIF(B9:C9,"<"&TODAY()-30) = 1 15 =COUNTIF(B1:C8,"Y")+COUNTIF(B1:C8,"N") = 2

SYBASE: update statistics commands

The update statistics commands create statistics, if there are no statistics for a particular column, or replaces existing statistics if they already exist. The statistics are stored in the system tables systabstats and sysstatistics. The syntax is:

update statistics table_name 

    [ [index_name] | [( column_list ) ] ]

    [using step values ]

    [with consumers = consumers ]

update index statistics table_name [index_name] 

    [using step values ]

    [with consumers = consumers ]

update all statistics table_name 

The effects of the commands and their parameters are:

·         For update statistics:

o    table_name – Generates statistics for the leading column in each index on the table.

o    table_name index_name – Generates statistics for all columns of the index.

o    table_name (column_name) – Generates statistics for only this column.

o    table_name (column_name, column_name...) – Generates a histogram for the leading column in the set, and multi column density values for the prefix subsets.

o    using step values – Identifies the number of steps used. The default is 20 steps. If you need to change the default number of steps, use sp_configure.

·         For update index statistics:

o    table_name – Generates statistics for all columns in all indexes on the table.

o    table_name index_name – Generates statistics for all columns in this index.

·         For update all statistics:

o    table_name – Generates statistics for all columns of a table.

o    using step values – Identifies the number of steps used. The default is 20 steps. If you need to change the default number of steps, use sp_configure.

NOTE: Use update statistics on huge tables before starting any bulk data actions on that table. It is one kind of performance tuning as it refreshes indexes so that it will get out of load which could be due to all recent transactions