Excel SUMPRODUCT with Criteria: SUM Alternate Columns based on Header and Criteria

Learn how to sum columns based on column header and on criteria in the rows. 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.

In this video I show you how you can use SUMPRODUCT to get the same result. The formula uses SUMPRODUCT with criteria in the rows and in the columns.

🚩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

[alexrosen]

With all due respect to the other Excel gurus but this is probably the best explanation of the sumproduct I have seen so far.

[sachinrv]

Fantastic Leila; I used to do it with SUMIFS and INDEX with MATCH; but SUMPRODUCT does it elegantly :) Thanks for the video.

[raghuv]

Sumifs + index match for beginners and intermediate users.
Sumproduct for Excel experts and advance users.

Great video. Thanks

[anshuiway]

I don't have any words to express what kind of your knowledge in Microsoft Excel. its marvelous, mind blowing, superb. SUMPRODUCT function is little difficult to understand by me but I try after watch this video and also use in my spread sheets.

[jayjaynator]

Love your video!
I am following your tutorials to manage collections of collectible objects I catalogue so I can value different collections.
Use it for my hobby mostly, haha

[solomonall]

As I always tell, your are the best in explaining the flow in much much better way. Thank you so much for your dedication

[martinmana]

I have been looking for this exactly for months. Thank you Leila! You are an expert.

[semperdiscendum]

A-w-e-some! Thank you so much. Please keep them coming! 👍👍

[diannedrechsler]

Thankyou so much for sharing, Leila, I've been looking for a solution to this exact issue for weeks.

[johng]

Thanks in a million. This is badly needed. Second to none. Grade: A++

[thepulkalya]

Because of you, I solve many excel problems in my office. You are really great. I wish you long life.😘😘❤️❤️

[Softwaretrain]

Your explanation about how does sumproduct works was excelent and i didn't know which one is faster. Thanks alot.

[kangsungho]

Your lecture always enlighten me with what I haven't known so far despite 15 years of using excel. So far I knew Sumproduct can be used only with same length of data arrays. Thanks again for your highest quality lecture ever.

[vida]

Many thanks Leila. I just love the way, you are explaining formulas/calculations

[humayunshakil]

Just love youyr vidoes and how easily you explain complex stuff. God bless you. Thnak you

[SanjayKumar-yxgc]

Thank you so much, Leila, I don't have a word to your great explanation, how to make us understand of sumproduct function. Really, you are one of my best excel guru.

[jadermsantos]

I was lost until I find this video. Leila I love you! Thank you!

[shirish]

Mam ! Thanks for your valuable guidance !

[Svbp]

I knew this usage of sumproduct instead of sumifs but not this column selection is great to know. I did not see anyone mention in the comments, but the main reason to use sumproduct instead of sumifs even though it is slower is because it is stable with external files. If we are doing sumif or sumifs from another file, it is very unstable and many times data disappears and we have to open the source file. With sumproduct you can link to many many files and still will work !

[MySpreadsheetLab]

sumproduct is super handy! Thanks Leila