The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. Dates are just serial numbers in Excel, so earlier dates are always less than later dates. In the above formula, any dates that are greater than or equal to the start date AND less than or equal to...Read more
Working from the inside out, the ISERROR function returns TRUE when a value is a recognized error, and FALSE if not.
When given a range of cells (an array of cells) ISERROR function will return an array of TRUE/FALSE results. In the example, this resulting array looks like this:...Read more
In this example, the goal is to sum the values in F5:F16 when the Color in C5:C16 is "Red" and the State in D5:D16 is "TX". This is an example of a conditional sum with multiple criteria and the SUMIFS function is the easiest way to solve this...Read more
Excel times are fractional numbers. This means you can add times together with the SUM function to get total durations. However, you must take care to enter times with the right syntax and use a suitable time...Read more
When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers for the range.
If you want only the first column...Read more
When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range with multiple rows, the ROW function will return an array that contains all row numbers for the range:
...Read more
When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers for the range. In the example shown the array looks like...Read more
When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range that contains multiple rows, the ROW function will return an array that contains all row numbers for the range. In the example shown the array looks like this:
{5;...Read more
The goal in this example is to reformat names that appear in mixed upper and lower case letters into "proper case", defined as each word in the name beginning with a capital letter. This can easily be done in Excel with the PROPER function.
The...Read more
All the hard work is done by the COUNTIF function, which is configured to count the values in the named range "substrings" that appear the named range "rng" with like this:
COUNTIF(rng,"*"&substrings&"*"))...Read more