Excel SUMIFS: Sum Alternate Columns based on Criteria and Header
HTML-код
- Опубликовано: 30 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Unlock the power of Excel in handling data and creating dynamic sums based on user selection. Learn to build an intuitive dashboard report that updates automatically with every change in parameters. Perfect for corporate professionals, data analysts, and anyone looking to enhance their Excel skills.
⬇️ Download the workbook here: pages.xelplus.com/sumproduct-...
Learn how to sum columns based on column header and on criteria.
To solve this with SUMPRODUCT: • Excel SUMPRODUCT with ...
What You'll Discover:
▪️ Dynamic Sum Creation: Learn how to sum varying columns based on user-selected parameters.
▪️ Data Validation: Implement data validation for refined user input.
▪️ Interactive Dashboard Reports: Construct a dashboard-type report for effective data representation.
▪️ Practical HR Data Application: Apply these techniques to real HR data scenarios.
This example shows you a set of HR data. The aim is to create a report where the user inputs the cost center and selects which information they want to see for the cost center - for example the total salary, benefits or bonus information. The challenge is to SUM based on criteria (cost center) AND based on column header selection.
The solution shown in the video uses SUMIFS together with INDEX MATCH to provide a moving sum. The 2nd argument of the INDEX function using area numbers is also used to show an alternate solution.
To learn more about the SUMIFS function, check out this video:
• How to Use SUMIFS, COU...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
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.
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!
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.
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..
Fully agree Sachin. As a team the formulas can achieve amazing results :)
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!
That's great! Good to have you here.
Brilliant video and I like the way of giving the lecture clear and slow, the result is easy to understand and remember :)
Thanks Maher. Glad you like it :)
Excellent explanation of this application of SUMIFS with Index/Match to determine the sum column. Much appreciated.
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!!!!
I'm very happy to hear that! Thanks for the encouraging comment and the support!
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!
Glad you like it. It's great to have you here.
Mind blowing techniques used to dynamic sum. Thanks a lot.
Thanks! This helped me a lot to find out how to sumifs with dynamic columns!
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.
It's my pleasure Ted.
still some good person exits who help people for nothing.. thanks madam. i am watching your video from far away....
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
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 :)
Hi Leila,
Really your videos are very useful for me!! I am looking forward More videos from you.
best teacher! Thanks for sharing this with us.
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!
I'm glad to hear that! Thank you for your comment.
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.
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....
the between bracket idea inside the Index function is new thing for me thank you so much Ms. Leila.
You're very welcome Ismail - yes that can come in handy... :)
it's really hard to find the best video like this, thanks for your good tips
What a wonderful tutorial with a very clear explanations, thank you so much.
You're very welcome! I am glad you like the video.
Very informative ....simple .....Clear .....good job leila madam
I love your way of teaching ..
your teaching technique can give me bettr job
I really thankful to you..
Regards,
Dev Bartwal
You're welcome Dev! Very glad to hear you find the content useful.
Hi Leila, It's really good tutorial and I have used this for my future use in office calculation.
You're welcome Prafull. Glad to hear that :)
MY GOOOOOOD, you just answered my excel nightmares question, I lost sleep to find it. You are great, thank you so much.
Happy to help! Now get some rest 😊
Thanks for this video it's so helpful
Thank you so so much... I was looking for this way from a long time....
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?
Many thanks for your valuable information.
Some fools dislikes the video... strange...Great video ma"am ...always enhance knowledge
Leila, you're brilliant!! Thank you!!
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,"]")))
You are great at explaining complex things, thank you!
I'm glad you find it easy to follow. Thank you for your support.
Super useful, as always. Thanks a lot!
Thank you so much, finding you on you tube is my luck. Your videos are very much helpful.
I'm very happy to hear that Surya! Glad you find the content useful.
thanks so much Leila am enjoying your simplicity
Awesome video: clever, clear, concise, and useful.
GREAT JOB! THANK YOU!
Super helpful for me... Thank you Leila..
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! :-)
Glad it was helpful. Great to have you here!
Most welcome , we are waiting your next video. Thank you so much.
Just uploaded it :)
This is super great and helpful! Finally found it! Thank you so much for the sharing~❤️
So much helpful! Thanks for the idea!
Never knew about that trick using index with sumif's to change sum range dynamically.
Thank You !!
You're welcome Harsha.
Thanks Leila for sharing this useful video. The reference section of index formula was a new thing to learn.👍👍👍
Glad you like it :)
Hi Leila,
ultimate teaching, even excel beginners can understand the dynamic rules....
Keep posting more dynamic video's
Thanks Deva. Glad you like it.
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.
Many thanks Lotfy - that's a very nice way of putting it :)
Thank you Dear, your videos are really helpful.
brilliant video, many thanks!
YOU KNOWLEDGE IS VERY HELPFULL, THANKS
I need to test it but you may have changed my life at work :)
Your videos are really helpful in my work.
Thank you (from India)
I'm glad to hear that! You're very welcome.
Wow, thankyou ma'am for amazing trick.
thanks mam....this video is very helpful for me ........
Thanks you!!!!! It's so helpful for me.
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
Thank you Leila . You are GREAT teacher .
You're very welcome Nazila. I'm glad you like the tutorial :)
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.
Thank you Ankur for your nice comment and for your support. I am really glad you find the videos useful.
Again thank you ma’am.
I agree with Lotfy Kozman: thanks for the plate of vitamins!!!
Mike the master of fat plates caught me here :)
You're welcome Mike. It was snowing here yesterday so vitamins are always good :)
Wow - snowing is fun, but mostly because we don't get much snow in Seattle.
Here we just can't wait to get some sun. It was a very cold winter this year...
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
Simply amazing
very great information, its help me too much in my work
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
That's a great tip too. Thank you for sharing.
Thanks for your help
It's a really helpful 🙏
Life saver! Thanks so much
Excellent. Thanks for sharing,
Thanks for this video it's helpful
This is awesome, thanks for a great tute.
Leila I am your fan ...u are too good 😘😘
Nice video Leila, cheers 👍
Amazing Explanation and i had learned maximum tricks from your videos. thanks and Good wishes for your health,
Thanks! Glad the videos are helpful.
Great way to explain!
Outstanding, thank you!
i love that shortcuts tips like de CTRL + 1 thank!!! excelente video
Thank u to help us a lot, may we know how to calculate random columns in same formula & same table like( E,G,H OR F,H)
Many Thanks, really great
video
You're very welcome Ahmed. Glad you like it.
Leila, you are awesome.
Hi, I recently discovered your channel and am absolutely in love with your videos. I have a question I'm hoping you can help with. Is it possible to switch between getting a cumulative or stand alone sum? e.g. Could I get a sumif of the only benefits for a particular cost centre or a sum of benefits and base salary for a cost centre?
Excellent Logic Mam, it help
Much appreciated !!! Really good !!
Glad you like the video.
Ur a nice teacher dear go on with success
Hi Leila,
Thank you so much! This is so helpful. I do have a question. How do I account for the situation when the column header repeats? For example, let's say in your example we have 2 columns for Base Salary. When I use this formula it only sums up the 1st instance (or 1st column) for Base Salary and it ignores the 2nd column. I'd love for it sum up both.
Mam thx for sharing knowledge
Yes , I got learn which is amazing with my work. Thanks lila gharani
You saved me, Thank you....
Thank you so much for this great video.
You r great
You're very welcome!
Thank you very much
Ur a life saver ! Simply awesome
Glad it helped!
You're God sent
Hi Leila- this is indeed a great learning for me, I was writing a long formula with If+sumifs but this seems to be very efficient. I have one question, in my case I have some other dataset (%calculation) in between the base salary benefits and Bonus, I tried the second approach of your formula but I am unable to get the result on the third selection. Could you please help
Very Helpful, appreciate if you have next video with dynamic selection with Horizontal Sumifs..
Leila, love ❤️ you for sharing this information.. I'm looking for exact excel formula for dynamic header selection in sumifs.
Awesome trick 👌👍
Hi, this video is very useful. I have a quick question though. Does SUMIFS also work if you're trying to sum rows and not the columns, and what would the syntax modification be then?
E.g if you have your data set up with dates as the columns and cost centres as the rows.
Thank you Leila,have learnt a lot from your lessons,but I have trouble creating a formula for instance Cells column A= Items list cells column B= Unit of measure''kgs,pcs,etc,which formula can i use if i just type items in cell column C to correspond it in cells column D with the same unit of measure in B
Excellent video,
Thank you so much Lelia . This was good example of sumifs, based on row & column criteria. I would like to share , we can also do same thing by sumproduct function. Right.
insightful!