1. COUNT
Formula: =COUNT(A1:A90)
The COUNT formula counts the number of cells that contain numbers, and counts numbers within the list of data. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
For example, you can enter the following formula to count the numbers in the range A1:A90.
In this example, if nine of the cells in the range contain numbers, the result is 9.
2. COUNTA
Formula: =COUNTA(A1:A90)
The COUNTA function counts the number of cells that are not empty in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.).
The COUNTA function counts cells containing any type of information, including error values and empty text (""). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells.
If you do not need to count logical values, text, or error values (in other words, if you want to count only cells that contain numbers), use the COUNT function.
3. COUNTIF
Formula: =COUNTIF(B2:B29,"Criteria")
The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify. For example, suppose you have a worksheet that contains a list of tasks in column A, and the first name of the person assigned to each task in column B. You can use the COUNTIF function to count how many times a person's name appears in column B and, in that way, determine how many tasks are assigned to that person.
For example:
=COUNTIF(B2:B29,"Ravi")
=COUNTIF(Range,Criteria)
range required - One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
criteria required. A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 23, ">23", C9, "oranges", or "23".
You can use the wildcard characters — the question mark (?) and the asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters.
If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Criteria are case insensitive; for example, the string "oranges" and the string "ORANGES" will match the same cells.
4. COUNTIFS
Formula: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
The COUNTIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
criteria_range1 required. The first range in which to evaluate the associated criteria.
criteria1 required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted.
For example, criteria can be expressed as 23, ">23", C9, "oranges", or "23".
criteria_range2, criteria2, ... Optional.
Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.
Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.
Each range's criteria is applied one cell at a time. If all of the first cells meet their associated criteria, the count increases by 1. If all of the second cells meet their associated criteria, the count increases by 1 again, and so on until all of the cells are evaluated.
If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
5. Sum
Formula : =SUM(number1,[number2],...])
The SUM function adds all the numbers that you specify within a range.
Each data range can be a range, a cell reference, an array, a constant, a formula, or the result from another function.
For example, SUM(A1:A9) adds all the numbers that are contained in cells A1 through A9. For another example, SUM(A1, A5, A9) adds the numbers that are contained in cells A1, A5, and A9.
If the data in the range is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
If the data in the range has error values, or has text that cannot be translated into numbers, Excel displays an error.
6. Sumif
Formula: =SUMIF(B2:B99,">9")
You can use the SUMIF function to sum the values in a range, that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 9. You can use the above formula.
In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B9, "orange", C2:C9) sums only the values in the range C2:C9, where the corresponding cells in the range B2:B9 equal "orange".
=SUMIF(range, criteria, [sum_range])
Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.
sum_range Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
It almost works same as COUNTIF function, but instead of count it gives sum as ooutput.
7. Sumifs
Formula: =SUMIFS(A1:A99, B1:B99, ">6", C1:C99, "<19")
Adds the cells in a range, that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A99 only when the numbers in B1:B99 are greater than six (6) and the numbers in C1:C99 are less than 19, you can use the above formula.
The order of the data can differ between the SUMIFS and SUMIF functions. In particular, the sum_range comes first in SUMIFS, but it is the third in SUMIF. If you are copying and editing these similar functions, make sure you put the formulas in the correct order.
8. LEN
Formula: =LEN(text)
LEN returns the number of characters in a text string, including spaces.
Example:
consider, cell A1 and A2 has below text respectively
A1 =How are you
A2 =Howareyou
=LEN(A1) will give you result as 11, as it also counts spaces between words
=LEN(A2) will give you result as 9, as there are no spaces
9. TRIM
Formula: =TRIM(A1)
You can use this functon to remove any space in a cell, except for single spaces between words. This formula is extremely useful because very often we run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of the data. This can create big mess if you are trying to compare using IF statements or VLOOKUP’s.
10. MID, LEFT, RIGHT
Formulas:
=MID(text, start number, number of characters)
=LEFT(text, number of characters)
= RIGHT(text, number of characters)
These formulas return the number of characters from a text string. MID gives you the specified number of characters from the middle of the word, LEFT gives you the number of characters from the left, and RIGHT gives you the number of characters from the right of the text string.
You can specify in the MID formula where to start with the start_number and then it counts the specified number of characters to the right of the start_number.
In the below example we have used the LEFT formula to get the first word.
A1 = "How are you"
=LEFT(A1,1) formula gives us the word "H" from "how are you".
Use the MID formula to get the middle word.
=MID(A1,5,3) formula gives us the word "are" from "How are you".
Use the RIGHT formula to get the last word.
=RIGHT(A1,3) formula gives us the word "you" from "How are you".
11. Concatenate
Formula: =Concatenate(A1," ",B1," ",C1)
Concatenate formula is used to combine two or more words in single cell.
Example: Cell A1 = Vinod, B1 = Shyam, C1 = Verma
with the above formula, we will get the result as "Vinod Shyam Verma" in single cell. The " " is used for creating space between two words.
This can also be acheived by directly writing formulas as:
=A1 & " " & B1 & " " & C1
here & is used in place of comma's to directly concatenate the words.
12. AVERAGEIF
Formula: =AVERAGEIF(range, criteria, [average_range])
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
In the AVERAGEIF function, the cells in range that contain TRUE or FALSE are ignored.
If a cell in average_range is an empty cell, AVERAGEIF ignores it. If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
13. AVERAGEIFS
Formula: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Returns the average (arithmetic mean) of all cells that meet multiple criteria.This is similar function to that of countifs and sumifs.
If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value. If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value. Cells in range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0 (zero).
Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell. Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range. If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value. If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.
Formula: =COUNT(A1:A90)
The COUNT formula counts the number of cells that contain numbers, and counts numbers within the list of data. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
For example, you can enter the following formula to count the numbers in the range A1:A90.
In this example, if nine of the cells in the range contain numbers, the result is 9.
2. COUNTA
Formula: =COUNTA(A1:A90)
The COUNTA function counts the number of cells that are not empty in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.).
The COUNTA function counts cells containing any type of information, including error values and empty text (""). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells.
If you do not need to count logical values, text, or error values (in other words, if you want to count only cells that contain numbers), use the COUNT function.
3. COUNTIF
Formula: =COUNTIF(B2:B29,"Criteria")
The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify. For example, suppose you have a worksheet that contains a list of tasks in column A, and the first name of the person assigned to each task in column B. You can use the COUNTIF function to count how many times a person's name appears in column B and, in that way, determine how many tasks are assigned to that person.
For example:
=COUNTIF(B2:B29,"Ravi")
=COUNTIF(Range,Criteria)
range required - One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
criteria required. A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 23, ">23", C9, "oranges", or "23".
You can use the wildcard characters — the question mark (?) and the asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters.
If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Criteria are case insensitive; for example, the string "oranges" and the string "ORANGES" will match the same cells.
4. COUNTIFS
Formula: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
The COUNTIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
criteria_range1 required. The first range in which to evaluate the associated criteria.
criteria1 required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted.
For example, criteria can be expressed as 23, ">23", C9, "oranges", or "23".
criteria_range2, criteria2, ... Optional.
Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.
Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.
Each range's criteria is applied one cell at a time. If all of the first cells meet their associated criteria, the count increases by 1. If all of the second cells meet their associated criteria, the count increases by 1 again, and so on until all of the cells are evaluated.
If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
5. Sum
Formula : =SUM(number1,[number2],...])
The SUM function adds all the numbers that you specify within a range.
Each data range can be a range, a cell reference, an array, a constant, a formula, or the result from another function.
For example, SUM(A1:A9) adds all the numbers that are contained in cells A1 through A9. For another example, SUM(A1, A5, A9) adds the numbers that are contained in cells A1, A5, and A9.
If the data in the range is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
If the data in the range has error values, or has text that cannot be translated into numbers, Excel displays an error.
6. Sumif
Formula: =SUMIF(B2:B99,">9")
You can use the SUMIF function to sum the values in a range, that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 9. You can use the above formula.
In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B9, "orange", C2:C9) sums only the values in the range C2:C9, where the corresponding cells in the range B2:B9 equal "orange".
=SUMIF(range, criteria, [sum_range])
Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.
sum_range Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
It almost works same as COUNTIF function, but instead of count it gives sum as ooutput.
7. Sumifs
Formula: =SUMIFS(A1:A99, B1:B99, ">6", C1:C99, "<19")
Adds the cells in a range, that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A99 only when the numbers in B1:B99 are greater than six (6) and the numbers in C1:C99 are less than 19, you can use the above formula.
The order of the data can differ between the SUMIFS and SUMIF functions. In particular, the sum_range comes first in SUMIFS, but it is the third in SUMIF. If you are copying and editing these similar functions, make sure you put the formulas in the correct order.
8. LEN
Formula: =LEN(text)
LEN returns the number of characters in a text string, including spaces.
Example:
consider, cell A1 and A2 has below text respectively
A1 =How are you
A2 =Howareyou
=LEN(A1) will give you result as 11, as it also counts spaces between words
=LEN(A2) will give you result as 9, as there are no spaces
9. TRIM
Formula: =TRIM(A1)
You can use this functon to remove any space in a cell, except for single spaces between words. This formula is extremely useful because very often we run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of the data. This can create big mess if you are trying to compare using IF statements or VLOOKUP’s.
10. MID, LEFT, RIGHT
Formulas:
=MID(text, start number, number of characters)
=LEFT(text, number of characters)
= RIGHT(text, number of characters)
These formulas return the number of characters from a text string. MID gives you the specified number of characters from the middle of the word, LEFT gives you the number of characters from the left, and RIGHT gives you the number of characters from the right of the text string.
You can specify in the MID formula where to start with the start_number and then it counts the specified number of characters to the right of the start_number.
In the below example we have used the LEFT formula to get the first word.
A1 = "How are you"
=LEFT(A1,1) formula gives us the word "H" from "how are you".
Use the MID formula to get the middle word.
=MID(A1,5,3) formula gives us the word "are" from "How are you".
Use the RIGHT formula to get the last word.
=RIGHT(A1,3) formula gives us the word "you" from "How are you".
11. Concatenate
Formula: =Concatenate(A1," ",B1," ",C1)
Concatenate formula is used to combine two or more words in single cell.
Example: Cell A1 = Vinod, B1 = Shyam, C1 = Verma
with the above formula, we will get the result as "Vinod Shyam Verma" in single cell. The " " is used for creating space between two words.
This can also be acheived by directly writing formulas as:
=A1 & " " & B1 & " " & C1
here & is used in place of comma's to directly concatenate the words.
12. AVERAGEIF
Formula: =AVERAGEIF(range, criteria, [average_range])
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
In the AVERAGEIF function, the cells in range that contain TRUE or FALSE are ignored.
If a cell in average_range is an empty cell, AVERAGEIF ignores it. If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
13. AVERAGEIFS
Formula: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Returns the average (arithmetic mean) of all cells that meet multiple criteria.This is similar function to that of countifs and sumifs.
If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value. If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value. Cells in range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0 (zero).
Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell. Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range. If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value. If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.
No comments:
Post a Comment