Grab the file I used in the video from here 👉 pages.xelplus.com/sumproduct-alternate-file

This video never gets old. Such a beautiful explanation on index and match along with sumifs for different columns.

Just one thing which I want to write"" Excellent". You are doing a great job for the Excel users/lovers/addicts. Waiting eagerly for your every video, your trick, your explanation, your logic. Hats Off!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

@LeilaGharani

7 жыл бұрын

Thank you so much Gopala. Very glad to hear you enjoy the videos :)

Hi, Leila, as always, thanks for simplifying my work. Just to add, I use Indirect to dynamically change the sum range, but given that it's volatile, I always sought a better way; yours is better.

Great work. Your step by step approach is so logcal. I had this working on my own spredsheet in nearly real time following your steps.

I have learnt a lot from excel videos, not simply usage of excel functions but also a lot of workaround solutions. Thank you so much for great efforts!

Hi Leila, this is again one of the fantastic example of integrated functions. In excel, I feel a function really has a limited usefulness when used by itself, but when used in combination of other related functions, it produces amazing results. Always love to watch your videos on excel. Keep sharing..

@LeilaGharani

7 жыл бұрын

Fully agree Sachin. As a team the formulas can achieve amazing results :)

Mind blowing techniques used to dynamic sum. Thanks a lot.

This is great stuff! So glad I stumbled upon your page. The way you clearly explain not only the "How" but the "Why" is a real value add. You have a new subscriber!

@LeilaGharani

4 жыл бұрын

That's great! Good to have you here.

Excellent explanation of this application of SUMIFS with Index/Match to determine the sum column. Much appreciated.

Brilliant video and I like the way of giving the lecture clear and slow, the result is easy to understand and remember :)

@LeilaGharani

7 жыл бұрын

Thanks Maher. Glad you like it :)

thanks!!!! for posting such brilliant tutorials, I have applied most of these in our day to day business decision models using excel and been immensely benefited, keep it up, cheers!!!!

@LeilaGharani

6 жыл бұрын

I'm very happy to hear that! Thanks for the encouraging comment and the support!

Thanks! This helped me a lot to find out how to sumifs with dynamic columns!

best teacher! Thanks for sharing this with us.

Mem seeing your video has made me a good knowledge of Excel

So very happy I stumbled upon this video. Your explanation abilities are exceptional. I’m an instant subscriber - thank you!

@LeilaGharani

4 жыл бұрын

Glad you like it. It's great to have you here.

Leila, you're brilliant!! Thank you!!

Hi Leila I've worked with Excel for many years but learn something new every time I watch one of your vids. Just one quibble. The singular of "criteria" is "criterion". Keep up the good work. Peter

@LeilaGharani

7 жыл бұрын

Hi Peter - Glad you like the videos and find the content useful. As for criterion - That's good to know - I always went with criteria for singular and plural :)

Many thanks for your valuable information.

Hi Leila, Really your videos are very useful for me!! I am looking forward More videos from you.

Thank you Dear, your videos are really helpful.

What a wonderful tutorial with a very clear explanations, thank you so much.

@LeilaGharani

5 жыл бұрын

You're very welcome! I am glad you like the video.

the between bracket idea inside the Index function is new thing for me thank you so much Ms. Leila.

@LeilaGharani

6 жыл бұрын

You're very welcome Ismail - yes that can come in handy... :)

thanks so much Leila am enjoying your simplicity

Thanks for this video it's so helpful

This is awesome. This is just what I needed to conditionally sum on variable columns. I will have to check out more of your videos. Great Job!

@LeilaGharani

5 жыл бұрын

I'm glad to hear that! Thank you for your comment.

Super useful, as always. Thanks a lot!

Hi Leila, It's really good tutorial and I have used this for my future use in office calculation.

@LeilaGharani

6 жыл бұрын

You're welcome Prafull. Glad to hear that :)

You are great at explaining complex things, thank you!

@LeilaGharani

6 жыл бұрын

I'm glad you find it easy to follow. Thank you for your support.

still some good person exits who help people for nothing.. thanks madam. i am watching your video from far away....

Awesome video: clever, clear, concise, and useful.

it's really hard to find the best video like this, thanks for your good tips

Most welcome , we are waiting your next video. Thank you so much.

@LeilaGharani

7 жыл бұрын

Just uploaded it :)

GREAT JOB! THANK YOU!

Very informative ....simple .....Clear .....good job leila madam

Thanks for another interesting video. As trainer, I appreciate your taking the time to work through the reasoning behind the formula. I am trying a new strategy with your videos. Instead of just watching the videos (and taking notes), I watch the introduction and see if I can derive your solution on my own. Sometimes I able to arrive at the same solution, sometimes I find a different solution and most of time I cannot figure out a solution. I took a slight different strategy solve the problem. I defined names for each of fields/columns containing values being summed and used the INDIRECT function to decide which field to use based on the name of ranges. Thanks again for a wonderful video and a good intellectual challenge.

@LeilaGharani

7 жыл бұрын

That's a great learning approach and also a great answer you came up with! It's great to hear your ways of getting to the answers for me and others too because it provides all of us different perspectives and methods to getting to the same place. Thank you so much for sharing....

Miss it's great that you are able to help so many people in their jobs and businesses, very inspirational to business people and others, Thank you.

@LeilaGharani

3 жыл бұрын

It's my pleasure Ted.

Thank you so so much... I was looking for this way from a long time....

@followthetruth3283

5 жыл бұрын

Thanks you so much for love my comment....... I tired this formula but its work only on same sheet. As long as I tried to get the result from one sheet to another. I got N/A.... is there any solution plzz? Can u help me?

Super helpful for me... Thank you Leila..

So much helpful! Thanks for the idea!

brilliant video, many thanks!

YOU KNOWLEDGE IS VERY HELPFULL, THANKS

Thank you Leila . You are GREAT teacher .

@LeilaGharani

5 жыл бұрын

You're very welcome Nazila. I'm glad you like the tutorial :)

MY GOOOOOOD, you just answered my excel nightmares question, I lost sleep to find it. You are great, thank you so much.

@LeilaGharani

4 жыл бұрын

Happy to help! Now get some rest 😊

I love your way of teaching .. your teaching technique can give me bettr job I really thankful to you.. Regards, Dev Bartwal

@LeilaGharani

7 жыл бұрын

You're welcome Dev! Very glad to hear you find the content useful.

Never knew about that trick using index with sumif's to change sum range dynamically. Thank You !!

@LeilaGharani

7 жыл бұрын

You're welcome Harsha.

Nice video Leila, cheers 👍

Thank you very much for sharing all your knowledge, I really appreciate it, I have learned a lot from you, and I would like to share this other option, using the titles of the columns in the table and obtaining the same result =SUMIF(Table1[Cost center],J6,INDIRECT(CONCATENATE("Table1[",K6,"]")))

Thank you so much, finding you on you tube is my luck. Your videos are very much helpful.

@LeilaGharani

6 жыл бұрын

I'm very happy to hear that Surya! Glad you find the content useful.

Some fools dislikes the video... strange...Great video ma"am ...always enhance knowledge

Thanks Leila for sharing this useful video. The reference section of index formula was a new thing to learn.👍👍👍

@LeilaGharani

6 жыл бұрын

Glad you like it :)

This is super great and helpful! Finally found it! Thank you so much for the sharing~❤️

Thanks you!!!!! It's so helpful for me.

Your videos are really helpful in my work. Thank you (from India)

@LeilaGharani

6 жыл бұрын

I'm glad to hear that! You're very welcome.

Hi Leila, ultimate teaching, even excel beginners can understand the dynamic rules.... Keep posting more dynamic video's

@LeilaGharani

6 жыл бұрын

Thanks Deva. Glad you like it.

Wow, thankyou ma'am for amazing trick.

Great job Leila, you offered a very healthy plate contains a lot of vitamins: Data Validation, Sumifs, Index (Array & Reference), and Match. This plate might be richer than the delicate Pivot Table plate. Thanks for your efforts.

@LeilaGharani

7 жыл бұрын

Many thanks Lotfy - that's a very nice way of putting it :)

Great way to explain!

Excellent. Thanks for sharing,

thanks mam....this video is very helpful for me ........

Many Thanks, really great video

@LeilaGharani

6 жыл бұрын

You're very welcome Ahmed. Glad you like it.

Simply amazing

Thanks for this video it's helpful

Life saver! Thanks so much

Leila, you are awesome.

Thank you so much! My level in excel is only intermediate and this video has helped me a lot in solving one of my excel tasks! You got another subscriber! :-)

@LeilaGharani

4 жыл бұрын

Glad it was helpful. Great to have you here!

All your videos are really very informative and educational. I like Excel and have learned a lot from you. Thank you so much for giving us such opportunity to learn from you.

@LeilaGharani

6 жыл бұрын

Thank you Ankur for your nice comment and for your support. I am really glad you find the videos useful.

@ankursingh35

6 жыл бұрын

Again thank you ma’am.

This is awesome, thanks for a great tute.

Outstanding, thank you!

I need to test it but you may have changed my life at work :)

very great information, its help me too much in my work

Hi Leila, Also, we can use for range selection with appling CHOOSE&MATCH functions instead of index. =SUMIFS(choose(match(K6;Table1[[#Header];[Base salary]:[Bonus]];0);Table1[Base salary];Table1[Benefits];Table1[Bonus]);Table1[Cost center];J6) That's all 😊 Thanks for your presentation

Leila I am your fan ...u are too good 😘😘

Thanks for your help It's a really helpful 🙏

Ur a nice teacher dear go on with success

Much appreciated !!! Really good !!

@LeilaGharani

5 жыл бұрын

Glad you like the video.

I agree with Lotfy Kozman: thanks for the plate of vitamins!!!

@LotfyKozman

7 жыл бұрын

Mike the master of fat plates caught me here :)

@LeilaGharani

7 жыл бұрын

You're welcome Mike. It was snowing here yesterday so vitamins are always good :)

@excelisfun

7 жыл бұрын

Wow - snowing is fun, but mostly because we don't get much snow in Seattle.

@LeilaGharani

7 жыл бұрын

Here we just can't wait to get some sun. It was a very cold winter this year...

@michaeldiamond2726

7 жыл бұрын

You may not get much snow in Seattle, Michael, but you get lots of rain, eh? It's been cold in San Francisco Bay Area this year we've had lots of rain. The rain actually took out some of mountain roads - they just washed down the mountain side

Amazing Explanation and i had learned maximum tricks from your videos. thanks and Good wishes for your health,

@LeilaGharani

4 жыл бұрын

Thanks! Glad the videos are helpful.

Excellent Logic Mam, it help

Thanks for your videos I have been watching some of your videos and find your channel a great place to learn new tricks. I would suggest using named ranges and the indirect function to reference the dynamic sum range in the formula. I have tried it many times and it works just fine

@LeilaGharani

6 жыл бұрын

That's a great tip too. Thank you for sharing.

You saved me, Thank you....

Ur a life saver ! Simply awesome

@LeilaGharani

4 жыл бұрын

Glad it helped!

Mam thx for sharing knowledge

Thank you so much for this great video. You r great

@LeilaGharani

5 жыл бұрын

You're very welcome!

Excellent video,

Awesome trick 👌👍

i love that shortcuts tips like de CTRL + 1 thank!!! excelente video

Thank you very much

Gonna use this to build a brilliant budget sheet

yours teaching projects nd your teaching way is both are superb I'm enjoying your teaching class 👍

@LeilaGharani

4 жыл бұрын

That's so great to hear. Thanks for the kind feedback.

@vijaysahal4556

4 жыл бұрын

@@LeilaGharani hi madam I m going to request thats plz make a video as a like original data I mean official work data..after thats use data base nd make a revenue % donet bar chart bcz its very useful my official presentation 👍

insightful!

Thank you very and much appreciate

Very useful

You're God sent

Excellent!

@LeilaGharani

7 жыл бұрын

Thank Alex.

Amazing, thank you!!!

@LeilaGharani

3 жыл бұрын

You're so welcome!

Very Helpful, appreciate if you have next video with dynamic selection with Horizontal Sumifs..

Yes , I got learn which is amazing with my work. Thanks lila gharani

Hope you post new more vdo. I like your work😍

Thank you .

@LeilaGharani

7 жыл бұрын

You're welcome Praveen