Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Count if row meets internal criteria
To count rows in a table that meet internal, calculated criteria, without using a helper column, you can use the SUMPRODUCT function. Context Imagine you have a table of sales figures for several products. You have a column for sales last month and a column for sales in the current month. You want...Read more
Excel formula: COUNTIFS with variable range
To configure COUNTIFS (or COUNTIF ) with a variable range, you can use the OFFSET function . In the example shown, the formula in B11 is: = COUNTIFS ( OFFSET ( B$5 , 0 , 0 , ROW () - ROW ( B$5 ) - 1 , 1 ), "<>" ) This formula counts non-blank cells in a range that begins at B5 and...Read more
Excel formula: FILTER on first or last n values
To FILTER and extract the first or last n values (i.e. first 3 values, first 5 values, etc.), you can use the FILTER function together with INDEX and SEQUENCE . In the example shown, the formula in D5 is: = INDEX ( FILTER ( data , data <> "" ), SEQUENCE ( 3 , 1 , 1 , 1 )) where data...Read more
Excel formula: Get name from email address
If want to extract the name part of an email address, you can do so with a formula that uses the LEFT and FIND functions. In the generic form above, email represents the email address you are working with. In the example, we are using this formula: = LEFT ( C4 , FIND ( "@" , C4 ) - 1 )...Read more
Excel formula: Last n days
To check if a date is within the last n days of today's date, you can use a formula based on the TODAY and AND functions. In the example shown, we are checking for dates in the last 7 days. The formula in D5, copied down, is: = AND ( B5 >= ( TODAY () - 7 ), B5 < TODAY ()) You can use a...Read more

Videos

In this video, we'll use MATCH to find the position of an item in a table, and the INDEX function to retrieve the value at that position in a two-way lookup.
Run time: 3:18
In this video, we'll look at how to use the SUMIFS function with an Excel table, with a side-by-side comparison without a table.
Run time: 3:35
In this video, we'll look at how use Boolean algebra in array formulas for AND and OR logic. This is a key skill when building dynamic array formulas.
Run time: 3:36
In this video, we’ll set up the XLOOKUP function to perform an approximate match in order to calculate a quantity based discount.
Run time: 3:22
In this video we look at how to group sample voting data into 10 year buckets, using a pivot table.
Run time: 2:47

Blog

What is an array formula?
In the world of Excel formulas, the term "array formula" is probably responsible for more confusion than just about any other concept. This is because the definition of an array formula has become mixed up with the requirement to enter some array formulas in a special way, with control + shift + enter.
Excel's RACON functions
There are eight functions in Excel that work differently than you might realize. The same features that were supposed to make them easier to use, can actually make them harder to use :)
This article provides examples of public Coronavirus data you can download to Excel with Power Query. Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook.
Tracking COVID-19 with Excel
A quick example of how to track testing for COVID-19 using Excel and publicly available data. In this project, the data is fetched and "shaped" with Power Query, then dropped back into Excel, where it can be refreshed with a single click.
Dynamic Excel offers 6 brand new functions that solve hard problems in Excel like sorting, filtering, and working with unique values. For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel.

Feedback

Many thanks.This site is pinned to my home page and has proven itself very useful. -Glynn
I felt compelled to send my thanks for your clear, informative, concise, and just very helpful videos. I have been thrust a data management assignment without training and far outside my usual duties, so these have been invaluable. -Zoë
Just found your site. Totally loving it. Great explanations! -Robert