To sum values based on a specified criteria or condition you use the SUMIF function, and its syntax is as follows:
SUMIF(range,criteria,sum_range)
Range is the range of cells that you want to be evaluated using the specified criteria.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added [...]
To get the week number of a given date, i.e. to find out where the week falls numerically within a year you use the WEEKNUM function.
Syntax
WEEKNUM(serial_num,return_type)
serial_num : is a valid date. E.g. August 15, 2008 or 12/10/2008.
Return_type : is a number that determines on which day the week begins. (1 = Sunday, 2 = Monday). [...]
The Analysis Toolpak is an Excel add-in program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first.
1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK.
Tip:
If Analysis Toolpak [...]
In a previous post I covered counting how often a value occurs within a range. In that post we looked into one criteria, and we used the COUNTIF function.
But what if we want to count cells that meet multiple criteria. We can not use the COUNTIF function in this case, so we will take a [...]
To count how often a value occurs within a range we use the COUNTIF function.
Because this function count cells based on a given criteria, that fact will help us limit the count to our specified value.
If in the example below we want to count how often “John” appeared in the list, then we will use [...]
If you look at many of Excel lookup functions you will find out that they either return a value or a position of an item. There is no function that returns the address where a lookup value is found.
Below is a code for a user defined function that takes two arguments: a lookup value and [...]
COUNT
Counts the number of cells that contain numbers within a given range. It can also accept a list of arguments other than cell ranges and counts numbers within that list of arguments. Use COUNT to find out how many cells or arguments that have numeric values.
Syntax
COUNT(value1,value2,…)
Read more …
COUNTA
Counts the number of cells that are not [...]
To count numbers greater than or less than a given number, you use the COUNTIF function.
As asn example if you have a list of numbers in a range of cells B1:B20 and you want to know how many of those cells have a value greater than 100, then you use COUNTIF as follows:
COUNTIF(B1:B20,”>100″).
On the other [...]
The COUNTIF function counts the number of cells within a given range that meet a given criteria.
Syntax
COUNTIF(range,criteria)
Range: The range of cells from which you want to count cells.
Criteria: The criteria upon which you want to base your count. It defines which cells will be counted.
The criteria can b a number, expression, cell reference, [...]
To count cells, within a given range or list of arguments, that contains data, you use the COUNTA function.
Data in this context includes error values, spaces and empty text (””).
Syntax
COUNTA(value1,value2,…)
Examples:
COUNTA(A1:A20) :counts any nonblank cells in a range of cells.
COUNTA(A1:A20, 7,”ABC”) :counts nonblank in a range of cells plus the second argument (7), and the third [...]
