Dynamic SUM Formula with XLOOKUP (feat TAKE and DROP)

Create a dynamic SUM formula with XLOOKUP in Excel.
The XLOOKUP function in Excel really changed how we lookup and return dynamic values. We no longer rely on the INDEX function so much and the gymnastics involved.
Learn more than 150 Excel formulas amzn.to/3Rg87Go
⌚ Video chapters
00:00 - Dynamic SUM with XLOOKUP formula
01:56 - Searchable list with dynamic SUM
02:18 - Sum the last 6 months only
03:33 - DROP function to remove the first column
04:26 - TAKE function to extract the last 6 columns
📚 Resources
Sample file - computergaga.com/_excel/files...
This video shows two examples of a dynamic sum formula. The first to sum all values from a given row and the second to sum the last 6 months only. The Excel formula will be fully dynamic.
Find more great free tutorials at; www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / alanmurray-computergaga
Instagram ► / computergaga1
Twitter ► / computergaga1
#xlookup #excelformulas #excel365

[@Computergaga]

📚 Learn more than 150 Excel functions with 'Advanced Excel Formulas' - amzn.to/3Rg87Go

[@ExcelWithChris]

Love these short "combination" formulas. Thanks!!!

[@Computergaga]

You're so welcome! Thank you, Chris.

[@vacilando86]

Last 6 months average, median, percentile or even forecast ets function can be used via this method. Brilliant

[@Computergaga]

Absolutely! Thank you for your comments.

[@Hello-bn2yc]

Thank you Andy for sharing that tip

[@Computergaga]

No problem 👍

[@nadermounir8228]

Thank u Alan for this great video 📹 👍

[@Computergaga]

My pleasure! Thank you Nader.

[@kebincui]

Very clearly and elegantly explained ❤👍. Thank you Alan.

[@Computergaga]

Thank you Kebin.

[@khanabdussabur8604]

Absolutely helpful, Thanks a lot.

[@Computergaga]

You're welcome! Thank you.

[@tomshaw9065]

I can already think of some applications of this for where I work. Excellent example to make us look outside our usual functions!

[@Computergaga]

Fantastic! Thank you Tom.

[@thebhaskarjoshi]

Loved this ❤

[@Computergaga]

Thank you 😊

[@MrDhunpagla]

More like these pls.... This is absolutely fantastic.... Thanks

[@Computergaga]

Thank you 😊 I hope to deliver more.

[@tanveerabbas3271]

great as always. this time you did not attach sample data

[@Computergaga]

Thank you Tanveer. The sample file has been added.

[@Seftehandle]

Great video as always. Video suggestion: explain the absolute and relative references in a formatted table

[@Computergaga]

Thank you 😊
I have a video here ua-cam.com/video/JjYbuHa1YYQ/v-deo.html
Absolute references in Tables are awkward.

[@SKL1412]

Good idea

[@Computergaga]

Thank you.

[@ExcelWithChris]

🙌🙌

[@andrewjohnston2586]

Great vid; why do you say the data is in a terrible layout for data analysis?

[@Computergaga]

Thank you Andrew.
Because the data is in a Pivot style layout with month names along the header row. Month names are data and should really be in a column named 'Month Name' so the data could be filtered, sorted or calculated using the month.

[@mazenzagha8811]

Dear sir
Thanks for this video
Sir could you please help me 🙏
Sir i need to save and print the form it self on one page only
Sir i try many times and i can't
Sir what is the codes for this
If you have vidio or link pls provided
Thanks again