Basic formulas

Basic formulas | Advanced formulas

The formulas presented in this category are easier to type and not complicated to understand, the sidebar displays all articles in this category.

Latest articles in Basic formulas category

Sum unique distinct numbers

The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]

INDEX MATCH – Last value

INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]

How to count blank cells

The image above shows the COUNTBLANK function counting empty cells in cell range B3:B14. Note that the COUNTBLANK function ignores […]

Count complete hours between two times

The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]

How to calculate the number of weeks between dates

The image above demonstrates a formula that calculates the number of complete weeks between two dates. Column B and column C […]

How to calculate the number of months between dates

The image above shows the DATEDIF function calculating the number of complete months between two dates. Column B and column C […]

How to calculate the number of years between dates

The image above demonstrates the DATEDIF function calculating the number of complete years between two dates. Column B and column C […]

How to count workdays between two dates

Excel contains a great function that easily counts the number of workdays between two dates, it even allows you to […]

How to replace part of formula in all cells

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

If cell has value

The easiest way to check if a cell has a value is, in my opinion, to use the equal sign […]

Count a given pattern in a cell value

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]

Extract k-th word in cell value

The formula displayed above in cell range D3:D9 extracts a word based its position in a cell value. For example, […]

If cell contains any text

The picture above shows different values in column B and a formula in column C that tries to identifies the […]

Count cells with text

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, […]

Count rows with data

The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]

Count cells equal to any value in a list

The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]

Get the latest revision

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

How to extract numbers from a cell value

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]

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 […]

Calculate specific WEEKDAYS between two dates

If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]

Extract last word in cell

The formula demonstrated above in cell range C3:C9 extracts the last word from adjacent cell in column B. =TRIM(RIGHT(SUBSTITUTE(B3, " […]

How to remove numbers from a cell value

The array formula in cell C3:C7 extracts everything except numbers from cell B3. The following formula contains the TEXTJOIN function […]

Extract first word in cell

The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) […]

LEFT function for numbers

The LEFT function allows you to extract a string from a cell with a specific number of characters, however, if […]

INDEX and MATCH – multiple criteria and multiple results

The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]

Count cells containing text from list

The array formula in cell F3 counts cells in column B that contains at least one of the values in […]

If cell contains multiple values

The array formula in cell C3 checks if text string in B3 contains all values in F2:F3.

Days between two dates

The DATEDIF function in cell E3 allows you to calculate days between two dates.

SMALL function – INDEX MATCH

The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]

How to AVERAGE time

Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes […]

INDEX MATCH – Case sensitive

The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

Match two columns

The array formula in cell D12 matches two values in two columns each and returns a value on the same […]

If cell equals value from list

Regular formula Formula in cell C3: =IF(COUNTIF($E$3:$E$5,B3),"Yes","No") The COUNTIF function counts how many values in E3:E5 match cell B3, it returns […]

Sum by group

To extract groups from cell range B3:B10 I use the following regular formula in cell B13.

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), […]

INDEX MATCH with multiple criteria

The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]

Sum numbers between two dates

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

If cell contains text

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 functions

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

Running totals

The SUM function in cell D3 uses only a single cell reference and still manages to sum current and previous […]

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]

Find last value in a column

The formula in cell D3 lets you get the last value in column B, it works fine with blank cells […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]

Count multiple text strings in a cell range

The array formula below counts how many times multiple text strings exist in a cell range. It is easy to […]

How to create name initials

The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

Sum cells based on criteria

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]

Running totals within date range

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

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 […]

Lookup two index columns

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Extract numbers from a column

Question: I want to extract all numeric values into a new column? If you have both letters and digits in […]

List values with past date

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Count specific text string in a cell

Question: How do I count how many times a text string exists in a cell value in Excel? Answer: The […]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

Create a date range [Formula]

Question: I am trying to create an excel spreadsheet that has a date range.Example: Cell A1 1/4/2009-1/10/2009 Cell B1 1/11/2009-1/17/2009 […]

Sum unique numbers

The formula in cell D3 adds all unique numbers in cell range B3:B12 and returns the total. Unique values are all […]

Count dates inside a date range

How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Find closest value

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

Calculate last date of a given month

The formula in cell C3 calculates the last date for the given month and year in cell B3. =DATE(YEAR(B3), MONTH(B3)+1, […]

Find latest date in a list

The image above shows a formula in cell D3 that extracts the latest date in cell range B3:B15. =MAX(B3:B15) The MAX […]

Sum only visible cells

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]