Excel Magic Trick 1452: Max Monthly Customer Total for Year: Array Formula or PivotTable?

See how to calculate the single largest Customer Monthly Total from 81 customers over a 12-month period.
Download File:
See two methods; 1) PivotTable Method, 2) Array Formula Method. The PivotTable Method is the easy way to accomplish this. The Array Formula method is a difficult Array Formula, but it can make all calculations in a single cell and will update instantly if source data changes. For this array formula you will learn some important concepts for Function Argument Array Operations and how to create a rectangular resultant array by carefully creating the Array Constants and Resultant Arrays with the correct Array Syntax. Learn that Curly Brackets house the array, Semi-colons represent Rows and Commas represent Columns. See the Excel Spreadsheet Functions: AGGREGATE, SUMIFS and EDATE.

Topics in This Video:
1. (00:04) File for download
2. (00:13) Introduction
3. (00:56) PivotTable Solution
4. (02:51) Array Formula Solution
5. (16:26) Summary

[johnborg]

I love these formulas !!! Every time i see these formulas I watch the video over and over again. I just LOVE THEM :)
I surely hope that we will have more of these in the future Mike :)

[vida]

Just amazing to generate whole pivot table in a single formula!

[roderickmose]

My take away nugget from this video is the Aggregate function. It's like an army swiss knife kinda function. AWESOME!!!

[MySpreadsheetLab]

Wow, that array was epic! I going to watch this again! Thanks Mike

[Al-Ahdal]

Excellent video, with awesome presentation as always.

[VS-rhrq]

Pivot tables...understood great stuff ...Sumifs formula...Going to pharmacy to get pain killers now

[ricksonsihombing]

is it possible to change the SUMIF function in the array formula with SUMPRODUCT? thank you

[deepakkanade]

Oops.... never knew that an array can be used inside sumifs... Great video.... completed the practicals before giving a thumbs up!!

[entertainmentgalaxy]

Super Duper like... Thanks for sharing and letting us know how to handle in both ways.
GBU

[levelzeroD]

Excelisfun and I are sitting in the same room right now!

[Ann-lggh]

awesome! love your videos! thank you very much.

[rrrprogram]

yeee... Again first like .. first show :D... I am loving it ..

[SujonAhmed]

Whose microsoft office u using this tutorial

[a]

Whew! I'll stick with the Pivot Table function.