Excel SUMIFS: Sum Alternate Columns based on Criteria and Header

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.

Learn how to sum columns based on column header and on criteria.

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:

🚩Let’s connect on social:

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

[gopalakrishnarao]

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

[peterwadsworth]

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

[tedsterrett]

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.

[teoxengineer]

Hi Leila,
Also, we can use for range selection with appling CHOOSE&MATCH functions instead of index.
center];J6)
That's all 😊
Thanks for your presentation

[UbaidUllah-njet]

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

[euniceesinam]

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.

[irislee]

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!

[hasanahmed-lslb]

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

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

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

[rakeshkumarmadella]

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

[mackenmd]

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

[sachinrv]

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

[ismailismaili]

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

[raghuv]

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

[vijaysahal]

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

[THLMITA]

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

[s-pc]

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…

[bigman]

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

[JRVariety]

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