The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

How to remove unwanted characters in a cell

Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]

If cell equals value from list

The array formula in cell C3 checks if cell B3 is equal to any of the values in E3:E5, the […]

If cell contains text from list

The array formula in cell C3 checks if cell B3 contains at least one of the values in List (E3:E7), […]

The formula above in cell D3 performs two different logical tests, if at least one of them is TRUE one […]

The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]

Use IF + COUNTIF to perform numerous conditions

The COUNTIF function allows you to construct a small IF formula that carries out plenty of logical expressions. Combining the IF […]

IF with AND function – multiple conditions

The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]

Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Create a unique distinct list and sort based on frequency

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]