The SUMIF function is a direct way to sum values based on a single criteria. If we want to sum or add values based on multiple criteria however, then we need to take extra steps. One alternative for this is to use the SUM function and the IF function.
In the example below we want to [...]
To calculate the number of months between two dates, there are two possibilities:
1. Both dates occur in the same year.
2. Dates occur in two different years.
Case 1 - both dates occur in the same year
Use the MONTH function to extract the month from each date, then subtract the two months from each other.
Example (using format:dd/mm/yyyy):
Suppose [...]
To calculate the difference or the number of days between two dates, you simply subtract one date from the other.
Example (using format:dd/mm/yyyy):
Suppose you have the date 25/11/2007 in cell A1 and the date 15/01/2008 in cell B1 (both cells formatted as date). To calculate the difference between these two dates you use the following formula:
=B1 [...]
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). [...]
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 [...]
It may be interesting to know that the Find option in Excel Edit menu is not limited to finding cell values, but it can also be used to search for text in a formula or a comment.
To search for a text in a formula follow these steps.
1. In the Edit menu click Find.
2. The Find [...]
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 [...]
