Excel SUMIFS: Sum Alternate Columns based on Criteria and Header

[@LeilaGharani]

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

[@1gopalakrishnarao]

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]

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

[@sachinrv1]

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]

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

[@raghuv7114]

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

[@peterwadsworth4607]

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]

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 :)

[@euniceesinam2573]

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.

[@TH3LMITA]

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,"]")))

[@LeilaGharani]

That's great! Thank you for this. I will pin this on top so others can see this alternate method as well :)

[@MIRZA1515]

@@LeilaGharani, would you please make a video for this formula, because i did not understand the difference.

[@ajayrungta5131]

I thought of this way initially, but yeah the formula is better, I think. So his formula takes use of the in-built name manager functionality of tables where table headers are automatically used as name for the corresponding columns. So in the formula instead of typing exact ranges like f2:f26 simply use that name. He referred the name with k6 input cell. Hope this clarify.

[@PedroDelfin]

I use this already, but can I have a 2 column sum or average range in a table?

[@ellisclyde9137]

you probably dont care at all but does someone know of a way to log back into an Instagram account? I was stupid lost the account password. I appreciate any help you can offer me

[@tedsterrett7204]

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]

It's my pleasure Ted.

[@irislee7999]

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!

[@mackenmd]

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

[@LeilaGharani]

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

[@bigman55434]

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

[@s1596-p8c]

Hi Leila, How does it work when the column selection has to be done based on conditions in first row and second row? For example: first row condition narrows the columns to a few and the second row condition brings down to one column to be selected. Looking forward for your answer…

[@reidsexton2869]

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]

That's great! Good to have you here.

[@resrussia]

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]

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

[@maherahmad6982]

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

[@LeilaGharani]

Thanks Maher. Glad you like it :)

[@shekonimusbaudeen3671]

Thanks very much for this insightful video. I just want to know if there's way to use this formula to return/sum the values between two dates say 29/01/2022 and 05/02/2022. This is assuming we've got dates in columns F:H. This will then bring the number of criteria to 3 - 'Cost centre plus the first and second dates.

[@ismailismaili0071]

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

[@LeilaGharani]

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

[@Dev_Bartwal]

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

[@LeilaGharani]

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

[@teoxengineer]

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

[@excelisfun]

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

[@LotfyKozman]

Mike the master of fat plates caught me here :)

[@LeilaGharani]

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

[@excelisfun]

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

[@LeilaGharani]

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

[@michaeldiamond2726]

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

[@LotfyKozman]

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]

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

[@JRVariety]

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

[@ColinKinsella]

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.

[@devadevendran7028]

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

[@LeilaGharani]

Thanks Deva. Glad you like it.

[@anasleco]

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

[@LeilaGharani]

Happy to help! Now get some rest 😊

[@hasanahmed-ls9lb]

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

[@vijaysahal4556]

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

[@LeilaGharani]

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

[@vijaysahal4556]

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

[@UbaidUllah-nj5et]

Mam b4 watching your videos i was of the view that index and match are used seperately and cannot be joined in any case and they cannot be use for typical problems solving...but now my view has been changed they are very useful and can ease our life to a great extent....Very useful video mam...

[@LeilaGharani]

Glad to hear this video helped :) Yes - I am a big fan of Index and Match and also Index mixed in with other formulas. I find it to be very powerful and flexible formula.

[@rakeshkumarmadella7955]

Simple suoerb mam.. take a bow.. hats off.. cant talk much about it.. simple wow

[@stewartketsuwan7792]

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.

[@mtariqraheem1]

Thank you Dear, your videos are really helpful.

[@automationguide3498]

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

[@LeilaGharani]

You're welcome Harsha.

[@ankursingh35]

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]

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

[@ankursingh35]

Again thank you ma’am.

[@nazilahajizadeh6932]

Thank you Leila . You are GREAT teacher .

[@LeilaGharani]

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

[@deba8296]

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]

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

[@qamarbhatti3884]

Great!!!! it's really helpful. Thanks for sharing.

[@mohammadraiesh4648]

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

[@praafullckashelkar3795]

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

[@LeilaGharani]

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

[@Poopshoot88]

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

[@marcvslicinivscrassvs7536]

This is incredible. I feel like I am sitting side-by-side with a real pro, learning the ropes. I have never seen input cells formatted that way. It was really nice how you showed how to format it. If you aren't on Udemy or Lynda yet, you should be. Cheers!

[@LeilaGharani]

I'm glad to hear you like the content! Thank you for your kind comment and support. Yes - I'm on Udemy :)

[@marcvslicinivscrassvs7536]

Leila Gharani Awesome I'm going to look and see your course catalog. Your way of explaining things is excellent. Good day! Enrolled!

[@LeilaGharani]

Thanks for the support. Which one did you pick?

[@marcvslicinivscrassvs7536]

"Advanced Excel: Top Excel Tips and Formulas" and "Visually Effective Excel Dashboards"

[@LeilaGharani]

Curious to get your feedback on these. Let me know on the Q&A board inside the course if you run into any questions.

[@अंकुरसिंहचौहान-फ9ण]

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

[@kevintrump6594]

thanks so much Leila am enjoying your simplicity

[@Pro-Tips-Tricks]

Hey Leila, I have a question for you, can we use the if function in multiple columns and multiple condition?

[@sabirrehman2706]

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

[@LeilaGharani]

Thanks! Glad the videos are helpful.

[@riteshgangwani115]

YOU KNOWLEDGE IS VERY HELPFULL, THANKS

[@SunnyAdhlakha]

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

[@LeilaGharani]

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

[@ls.vinicius]

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

[@0tysons]

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

[@suryachitra8895]

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

[@LeilaGharani]

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

[@ciprryenutza]

hi. which formula would you use if same dataset but you would actually have 2 columns as Base Salary? how would you sum up all the values from the columns Base Salary based on a row criteria? I have a similar situation, but I actually need to sum up by financial year, year 1, year 2 etc but still apply a row criteria. Hopefully there is a solution.

[@galporgy]

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

[@mando388]

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

[@allo801]

brilliant video, many thanks!

[@elinakulashkina5034]

You are great at explaining complex things, thank you!

[@LeilaGharani]

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

[@alfarhangroup505]

Ur a nice teacher dear go on with success

[@mohammedsharaf5609]

Hi Leila , thanks a lot for sharing This really helps a lot, i have a question though. Let say that i have a drop down list for cost centers and i want to see one by one for base salary and total as well, how can i do it since my criteria is limited to single cost center every time ? Thanks again.

[@khawarameer]

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

[@LeilaGharani]

Glad you like it :)

[@manissery1956]

Thanks a lot Leila. This is very helpful. Also, can we apply same method for sumproduct function as well??

[@nayemahmednafi2248]

the hottest excel teacher on earth ..... with brain with beauty .... this video really helped me today

[@mahneelmusa7469]

Hi Leila, how to sum alternate columns based on one single critera? For instance, if I were to just add the base salary and bonus of a cost centre, how would I do it?

[@kolagilbert1215]

Excellent. Thanks for sharing,

[@sushantsourav1259]

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

[@letlhogonolompetsane165]

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.

[@bobtheauditor08]

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]

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

[@AweshBhornya-ExcelforNewbies]

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]

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

[@khalid81320]

very passionate attention to details, always use your tricks at the office to amaze my friends and recommended and promote your channel to them videos are for free, Jazak Allah khairan this is a minimum to your efforts and may Allah rewarded you with Janna inshae Allah if please can share videos about P&L, manpower cost budgeting vs actual either dashboard or data analyses formulas "I know it`s a big topic " but your generosity goes beyond this world :)

[@LeilaGharani]

Thank you so much for the support Khalid. Will add your suggestions to my list of upcoming videos.

[@khalid81320]

chapeaux

[@skpolok5174]

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)

[@arbazahmad7177]

Great 👍. But I have a problem if I make a drop-down list on column headers basis and then I select cost center header from that drop-down list and I make one more drop-down list on basis of previous selection for example if I select cost center from 1st drop-down list then 2nd drop-down list will show entire cost centre data and I took 809 from 2nd drop-down list then I want to sum of all (base salary, benefits, bonus) in 3rd cell. Can you please help me on this.

[@JSmith-xq3uw]

Life saver! Thanks so much

[@tahirshah6979]

Leila. Do you think that your tips and tricks, analysis, graphics, techniques, shortcuts, dynamic views, VBAs etc through videos leads an individual to data scientists/ data analysts?

[@LeilaGharani]

I guess it depends how the analysis is done. I hope that if it's in Excel that you get some basic toolbox to at least get started.

[@prasannaganesh3824]

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

[@natalieharrison5202]

Gonna use this to build a brilliant budget sheet

[@skaurb3353]

Thank you very helpful Where do you learn all these formulas with different functions. It’s amazing

[@sathyanarayanansatagopan9069]

Thank you very much . Please explain how to use standard formula for totalling date-wise debit and credit columns and balance in next line, like cash book in tally.

[@santoshgnaga8540]

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

[@prince4x4]

Thanks for your share this help me a a lot , how about if me still have more criteria for check the column.

[@ollysoares]

Ur a life saver ! Simply awesome

[@LeilaGharani]

Glad it helped!

[@MIRZA1515]

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

[@LeilaGharani]

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

[@followthetruth3283]

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

[@followthetruth3283]

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?

[@nilanthaheendeniya1692]

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

[@rahuljha7708]

Thanks for your help It's a really helpful 🙏

[@bmagaroB]

Hi Leila thank you so much for all your tutorials. Could I ask you a question please? Say in cells F1:H1, you had Month 1, Month 2 and Month 3. and these months all belong to Financial year 1. I would sum up with the same criteria range of Column A please? Basically I would have months to be summed up by FY 1-2 3 etc. I hope it makes sense

[@solomon14all]

you wont believe me when is say this. this is the function i was looking for long time. amazingly explained as always. what else i can say mam other than thanks for your generosity. My friend have solved this issue by using indirect function nested with sumifs it is a bit though.....

[@LeilaGharani]

That's great Solomon! Very happy to hear you found what you were looking for :) . Thanks for your feedback.

[@TH3LMITA]

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,"]")))

[@shauniepmusic]

Hi Leila, thanks for all your work on your videos. I have come up against a problem with the SUMIFS formula on a part of the worksheet I am using in conjunction with your SUMIFS video. My sum_range is a selection of dollar amounts in a range of rows and columns but not all cells have a figure in them. This depends on an expense being entered under an expense category for the month or not. Therefore some cells are empty. I followed your video and the SUMIFS formula worked for all my other sums except when I tried using it with my expenses range. I guess it may be because of rows and columns and some cells empty. I did want to use the date criteria as well because my excel cash book operates in quarters of the year. Jul-Sep, Oct-Dec etc... I have searched around a bit on youtube and don't seem to be able to find anything - and I like your approach and you explain things really well. Any tips for me for this? Happy to send an example of the worksheet.

[@AbuTalha-eo7pr]

very useful; Please zoom in the result at the bottom for more clarification of the same result...

[@pinkeishz9106]

Hi Leila, I must say it was very efficient trick👍 I've two questions: 1) Doesn't table make file heavy with large data? 2) I regularly work with large data where I need to identify duplicate entries with combination of two or three fields. E.g. I've data in 26 column and 50k rows, I use following formula in column AA =B2&C2&D2 and in column AB I use =Countif(A$1$:A2,A2) and drag it to 50k rows which makes filters and others options very slow. Overall file becomes slow and heavy, any other way to do this?

[@KedarKulkarni1]

Hi @Leila I have been trying to use SUMIFS to evaluate as good as 4 various conditions before excel can provide Sum of the amount in rows. However, I am unable to get the exact result. All I am getting is 0 only. I did check if my source data has different formatting than the criteria but there was no difference found. Appreciate your help on this.

[@ahmedfadali4064]

Is it possible to use sumifs() to get the sum of more than one column in a table according to certain criteria, other than repeating the sumifs() for each column separatly? that would be great if you helped me in that, I already learned smart things from you.

[@dominickmilano]

Outstanding, thank you!

[@yousuflalani6469]

Mam thx for sharing knowledge

[@jelajahsebanten]

Good sharing, my sister

[@ZiadSourany]

Hi, what is I want to add 2 ranges instead of 1, what will be the right formula and how to write it. As example SUMIF requires 1 range, I want to add 2 range from different sheets with 1 criteria and 1 sum range. How to do it?

[@SanjayKumar-yx6gc]

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

[@LeilaGharani]

Just uploaded it :)

[@PMsPoints]

Many thanks for your valuable information.

[@christoperwijaya15]

So much helpful! Thanks for the idea!

[@yvonneyu6688]

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

[@bhushanshanbhag1302]

Hi Leila, this was very informative and helpful. If I wanted to add columns as well to the table & want the data validation to automatically reflect it, what is the simplest way? thanks

[@LeilaGharani]

You're welcome Bhushan. Easiest would be to use the first version of index formula and insert the column in the middle of the other ones - this ensures your validation list is updated automatically and also your formulas are updated automatically.

[@bhushanshanbhag1302]

Wow that was smart. Thanks again. Adding at the end and getting it updated takes so many steps.

[@nileshkIndia]

I am watching your video's and it's really helpful.

[@LeilaGharani]

Glad you think so!

[@nileshkIndia]

@@LeilaGharani I need your help to make data drill down to their respective parameters. Like if I select product>> state >> district then in tabular format first state will come followed by district in next column, then Fy18, Fy20 & industry growth but when I provide sumifs function to it value arrive with combination of of all 3 but not able sort by top and bottom Industry growth in sumifs. Could you help me in this or any alternate to it. How I can rank to it in data dump

[@nileshkIndia]

And if I want share sample details how can I do

[@nileshkIndia]

@@LeilaGharani I am really big fans of yours. The way you tought those concept my soul dilutes in such way, my mind can start applying it even I woke up in the morning.

[@VISHNU-rz1io]

Leila, love ❤️ you for sharing this information.. I'm looking for exact excel formula for dynamic header selection in sumifs.