Dynamic Yearly Sales Report with Excel’s Dynamic Spilled Array Formulas. Excel Magic Trick 1651

Learn how to create a Yearly Sales Report with Excel’s Dynamic Spilled Array Formulas. Learn about the new Dynamic Array Functions: UNIQUE and SORT, and see how the old standby Excel Functions YEAR and SUMIFS to build a Dynamic Report that updates when new data is added.
1. (00:01) Introduction
2. (00:27) Extract Unique List of Years from Data Column with the Excel Functions: YEAR, UNIQUE & SORT.
3. (01:20) Formula to Calculate Yearly Sales using the Excel Functions SUMIFS and DATE. Learn about Comparative Operators, the Join Operator and the # Symbol to refer to a Spilled Array. Learn about how to use a lower and upper limit in the SUMIFS function to add only sales for a given year.
4. (02:40) Add Conditional Formatting to Spilled Arrays. Learn how to use a Logical Formula and the ISNUMBER Excel Function.
5. (03:34) Add Number Formatting to Spilled Arrays.
6. (03:40) Add new data and test formulas.
7. (03:52) Summary and End Video Links

[excelisfun]

Topics:
1. (@) Introduction
2. (@) Extract Unique List of Years from Data Column with the Excel Functions: YEAR, UNIQUE & SORT.
3. (@) Formula to Calculate Yearly Sales using the Excel Functions SUMIFS and DATE. Learn about Comparative Operators, the Join Operator and the # Symbol to refer to a Spilled Array. Learn about how to use a lower and upper limit in the SUMIFS function to add only sales for a given year.
4. (@) Add Conditional Formatting to Spilled Arrays. Learn how to use a Logical Formula and the ISNUMBER Excel Function.
5. (@) Add Number Formatting to Spilled Arrays.
6. (@) Add new data and test formulas.
7. (@) Summary and End Video Links

[blinckyb]

this chanel is simply a gold mine, lots of thanks!!!

[simfinso]

Very Nice , Easy to understand & Useful.Thanks for posting

[mireillecantrell]

Love this video! Explained exceptionally well!!!

[monicazheng]

I learn something new from you every day, thanks

[muhammadnurhakimmohdderis]

You really inspire me to work better with excel... Thank you so much 🌟🌟👍

[clairezhang]

Thank you Mike for another awesome video.

[ismailismaili]

Easy, fun, and smart as well. thank you Mr. Mike

[dennychow]

You are a master, you make me look like a newbie on excel 😔. You 🔥🔥 man

[mattschoular]

Fantastic. Thanks Mike. It's a great time for learning.

[sanduzzi]

Your videos are always so helpful, thank you!

[sasavienne]

Great. Thank you Mike. I have just had some fun with the new funktion *LET* and it is amazing..

[darrylmorgan]

Boom!These Dynamic Functions Are Awesome..Thank You Mike:):)

[msantosh]

It was new and fun learning for me, in office 365. Thanks for putting up this video.
Excel is fun with Mike girvin

[aguerojg]

Thank you very much Mike, you always provide a new insight, that's amazing!

[wayneedmondson]

Hi Mike.. thanks for the challenge today.. great solution! Just for fun, here is mine using old school array formulas:
For the Year column in E4 (copy down): =IF(ROWS(F$4:F4)>SUM(--(FREQUENCY(YEAR(fSales[Date]), YEAR(fSales[Date]))>0)), "", AGGREGATE(15, 6, YEAR((fSales[Date])*(1/(FREQUENCY(YEAR(fSales[Date]), YEAR(fSales[Date]))>0))), ROWS(E$4:E4)))
For the Sales column in E5 (copy down): =IF(E4="", "", SUMPRODUCT((YEAR(fSales[Date])=E4)*(fSales[Sales])))
I applied your conditional formatting and appended the New Data to the bottom of the table and everything worked the same as with your dynamic array formula solution. Thanks for the workout.. keeping the mental Excel muscles strong while we all wait out the current crisis. Thumbs up for ExcelIsFun!!

[tedbrodsky]

Great scenario! We can do more if we learn this!

[MalinaC]

Thumbs up! It would be cool to put # sign into conditional formatting... :)

[markpodesta]

Good! Would be nice if Excel produced a formula for totals at the bottom of dynamic arrays.

[sabrinalimage]

Awesome video! Do you have any suggestions on how to account for a leap year? Especially if your sales report is dependent on specific days? Thanks for all that you do!!!