Excel Array Formulas Explained with MIN and IF Functions (Part 1 of 5)

In this video you will learn how array formulas work in Excel and how to calculate the Minimum of one column based on criteria from another column.

This is the first video in a series on solutions to the Formula Challenge to find the first sales transaction for each month of the year. There were over 100 solutions submitted on the blog page, so I created a series of videos to explain the most popular solutions.

The most popular solution was to use a MIN IF array formula. This first video explains how array formulas work with a simple example.

I also explain how to enter the array formula with Ctrl+Shift+Enter. We then use the Evaluate Formula window to step through each part of the formula and learn how Excel calculates arrays.

In the next video I explain how to combine the MIN IF with VLOOKUP or INDEX MATCH functions, to return a matching result from another column.

We will also look at NON-array solutions withe AGGREGATE and MINIFS functions in future videos.

Additional links:

00:00 Introduction
00:25 Find the First Transaction for Each Month
02:35 Array Formulas Explained
05:46 Bonus – AGGREGATE with Multiple Criteria

[juliecongress]

The explanations are incredibly user friendly, the speed perfect, and the examples spot on. I plan on viewing all videos from excel campus. 10 out of 10 recommend!

[peterbell]

How does this channel not have like 10 millions subs? I watch them at work, because they help me become a better and more productive employee, with the Excel knowledge I gain!

[hanneke]

Hallelujah, I finally get array formulas! Thank you Jon! ⭐

[colinhodkin]

Thank you for explaining this so well, I'm looking forward to seeing more of your videos

[shout-style]

Amazing Tutorial for array. Simple but powerful

[johnq]

Thank you John, very helpful video as I begin to explore array's in excel

[MrHugosky]

I love your video and explanation! Thank you very much in deed!

[IBITInformatica]

great explain... step by step interactions as it should be, , ,

[Amr-Ibrahim-AI]

Thanks a lot for simplifying this concept of Array formulas!

[kebincui]

Thanks Jon, very clear tutorial, easy to understand👍🏻👍🏻👍🏻

[krishgangs]

Brilliantly explained..clarified all my doubts on the basics

[danielsossa]

Gracias Jon! Siempre encuentro soluciones contigo.

[joem]

One little bit of feedback you may also press the Enter button (in lieu of the space bar) to advance the Evaluate window to the next step. But I like the spacebar because it is easier to hit.

[andrewphillips]

Excellent. I really appreciate the help.

[taimoor]

apply lint formula to extract Date from Text

[ravishankargupta]

Nice video! I want to learn about all functions related to array, so can you please make a video or provide me like of the videos by which i could learn the same.

[bhupatparmar]

Thanks, and was easy to understand. try to give 2 to 3 examples

[sowmyarajendran]

Thank you for the detailed explanation and examples! It's very helpful.

I'd like to apply this to a specific scenario. I need a formula that can find the month(s) with the highest amount spent across different months. It should also account for situations where multiple months have the same highest spending amount.

Could you please help me with a formula for this case?
I tried this formula, but it throws a formula parse error: =ArrayFormula(QUERY(C21:D32, MONTH(B21:B32)=max(VALUE(D21:D32))group by MONTH(C21:C32)))

[himanigoel]

The concept has been explained very simply but I have a doubt will the formula work if there is blank rows in comparison column?

[klinikutamamediktama]

It's not working in my ms.excel 2013 version.. any suggestion??