Dynamic SUM Formula with XLOOKUP (feat TAKE and DROP)

[@Computergaga]

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

[@kebincui]

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

[@Computergaga]

Thank you Kebin.

[@Hello-bn2yc]

Thank you Andy for sharing that tip

[@Computergaga]

No problem 👍

[@khanabdussabur8604]

Absolutely helpful, Thanks a lot.

[@Computergaga]

You're welcome! Thank you.

[@nadermounir8228]

Thank u Alan for this great video 📹 👍

[@Computergaga]

My pleasure! Thank you Nader.

[@MrDhunpagla]

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

[@Computergaga]

Thank you 😊 I hope to deliver more.

[@ExcelWithChris]

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

[@Computergaga]

You're so welcome! Thank you, Chris.

[@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 😊

[@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.

[@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 kzbin.info/www/bejne/gJu8k6h-lpaMj7M Absolute references in Tables are awkward.

[@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.

[@shoaibrehman9988]

It is gr8 video, i am wondering if a criteria to choose 3, 4 random months wich is not in sequence, how can we achieve the results. It would be great if you can able to explain.

[@Computergaga]

Thanks, Shoaib. We could use a formula such as the following. This fomrula extracts 4 random months for summing. =SUM(CHOOSECOLS(tblSales,RANDARRAY(,4,2,13,TRUE))) RANDARRAY generates a random array of integers between 2 and 13 (Jan to Dec in the table), then CHOOSECOLS creates an array uses these integers as indexes of columns in tblSales. Finally, SUM does its thing.

[@shoaibrehman9988]

@@Computergaga Thanks

[@SKL1412]

Good idea

[@Computergaga]

Thank you.

[@tanveerabbas3271]

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

[@Computergaga]

Thank you Tanveer. The sample file has been added.

[@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

[@ExcelWithChris]

🙌🙌