Excel Magic Trick 714: Three Criteria Lookup Adding SUMIF, INDEX, MATCH, SUMPRODUCT (2 examples)

NICE VIDEOS

It's astounding how you build all this logic when constructing a formula Mike. I've been trying to break this formula down and it just takes ages for me to understand it in a digestible manner.

Thank you so much for such a wonderful and useful video! Would you please let me know if you have a video that shows how to do the same in Excel Power Query? Thank you so much! Eleonora:-)

useful thanks

Thanks for the video! Great help!

Can you do a tutorial in this scenario. All in One row in different column: Subname#1 / Subcost1 ; Subname#2 / Subcost2 ; Subname#3 / Subcost3 ; Subname#4 / Subcost4

*************************************************************************

Sumproduct is brilliant ) old school though but still like to use it. Thanks Mike for your hard work throughout all these years :)

In this case, =SUMIFS wouldn't be easier? You don't even need to create a JOIN column nor =SUMPRODUCT.

What if we have a very large table that we can't reference each cell individually.
e.g. I have a sales report table containing country, gender (m/f) and the revenue generated by each gender from different countries. I need to sum up the revenue from males from a particular country, how do I go about this? I used SUMIFS without Index match and had to change the condition for each country, but I think there should be a way to use sumifs together with index match so that I can copy the formular along.

Great Knowlege, Thanks

Thank you very much for this content. Very simple to understand and implement. It was very helpful for me to follow your step by step procedure. Keep posting ! Thanks !

You are my savior, this saved a long long day, thank you. You dont understand how much stress you took off

Amazing, Thank you.

Even if you think you're an Excel pro, when you come on this channel you start to second guess yourself

Nice Work... Thanks

october sancho 10
october sancho 20
october sancho 30
october sancho 40
october neymar 50

how could i calculate the top 3 scores for sancho in october? i have a data set where 1000 players in october and i need to sum the top three for each player for that month. i tried to do this =IF(AND(E17=C17:C21,F16=B17:B21),SUM(LARGE(D17:D21,{1,2,3}))"") but due to the last part of the if function since it says neymar at the bottom it leaves it blank

Brilliant, makes it sound so simple!

Pudukku...sounds Great

thank you so much for this video! you have gotten me out of a pickle!!!

thank u so much!! but how if states are california and oregon?

Mike, you did it again. I had no idea how to sum up index and match and you explained it so easily. Outstanding!!!

very nice

Very helpful!

Thank you so much for your video... It was very helpful for complicated situations.

example for "countifs with variable table column and rows simultaneously" ???

Thanks for the explanation. I have 2 doubts here. Can we use SumifS function instead of this and second what if I need to use only index match function without using concatenate function where there are two criteria one which is horizontally placed and other which is vertically place,pls help

Hi, I have 2 headers as criteria, same as shown but month wise, pl suggest, how to incorporate 2 way headers in match function

Thank you for you videos, Please can you point me to the correct video for this problem. I have a sheet with both Inventory and Purchases on it, i want to calculate stock when entering a new purchase. That is when i enter a new item and i purchased 10 more it updates the Inventory.
My inventory list (Item Code) is in Column A( 3 to 24) and the Quantity is in B (3 to 24)
I have created a drop down menu in the Purchase column for the item code.
I now need to enter a value in the purchase quantity column G3 (3 to 24) and this should add that value to the Quantity in column B.

I wish I could like this a thousand times. Thanks for your videos!

How would you change the formula if there were 2 california columns?

This is Fantastic! Really Helped me save a ton of time. Thank you

I have 7 years of date for different companies (data range is different for each company e.g. for company A I have data from 2007-2013 and for B I have from 2003-2009), the data value changes in a certain year for each company (for example for first company the change happens in 2010 and for the second it happens in 2006). I have the date of change and already concatenate it with the company's unique code. I want to sum 3 years before change and 3 years after changes. Please help me with that.
I appreciate.

You are really an ace.. hates off

Your help is greatly appreciated!

Hi Mr ExcelisFun. Thanks for this video. However, I am looking for a formula that helps me solve the below puzzle.

I have a data which has three columns on worksheet "sheet1" with titles: "Month (ie January to December occurring multiple times), Account Code (NB: Account code could be for example 1001 to 1009 also occurring multiple) and Amounts varying for each lines. And on "worksheet2", I have one column and one row. The column is "Account Code and the row is "Months" (ie January to December).
My question is that am trying to find a combination of formula that will calculate the sum of "account codes" for individual codes (1001 t0 1009) in worksheet1 and sum them by months in worksheet2. That is, for instance, look up all code 1001 for January in worksheet1 and take all their total sums and put it below row January in worksheet2, then do the same for code 1002 and so on. I wouldn't mind sending you a template if I can get your email address. Thank you so much.

Iife saver tricks. thanks a bunch.

Hi, is it possible for you to provide assistance with a formula that is not returning the results? I have produced a table which is effectively the same as you have in Excel Magic Trick 714, but the formula is always delivering "0". I think I am very close.

Thanks for this!! very helpful.

Thank you sir. I would you say you're the best. You've covered almost all on adv Excel. Loads of love:-)

Perfect!!!

Thanks for this video, it is very helpful. I am trying to use the match function to provide values that are greater than or equal to numeric values. For example, if you replaced the States with weeks numbers (1,2,3,4,), I need to return all dollar values that are greater than or equal to week two as well as those that are less than week two. I know that you can use the 1 or -1 as the last argument for the match function but my research online makes me believe that the array must be sorted in ascending order for less than and descending for greater than. Any ideas of how to solve for this? Thanks again

Hello, i wanted to ask you, if you want to sum (QUADSR1 and QUADSR2) for california ??

Fantastic!  Just the formula that I needed.  Thank you so much! :)

Thanks a lot!

Thanks a lot. This really helped me today.

AWESOME! With the help of your video I managed to find a 3-minute-solution to a problem, that I already already spent hours on before!

Thank you very much, I have been looking for this solution for so long and this is exactly what i need!! Have been watching your seminars and they are ultra-helpful!! Thanks a lot again

Awesome; saved again by your archives. The sum-product part 2 is really clever.

in this same function how to add two date between criteria