Excel Magic Trick 1368: How To get SUMIFS to Calculate Sum Of Amazon And Google Under Each Region

Download Files:

Start File and Finished File:

See how to use the SUMIFS and SUMPRODUCT functions to Calculate Sum Of Amazon And Google Under Each Region. Properly you will learn about OR Logical Tests and AND Logical Tests so that we can ask the correct question as: “How to Add Sales for 'Amazon OR Google' AND West”. Learn how to make a Function Argument Array Operation in the criteria argument of SUMIFS to get SUMIFS to calculate an OR Logical Test (OR Criteria) as well as the default AND Logical Test (AND Criteria). See how to use SUMPRODUCT to add the results of an Array Operation, rather then the SUM Function.

[mahbubhossain]

Lam Kar, your alternative solution is awesome too!

we can solve it in this way also (almost similar) by using only SUMPRODUCT function:

SUMPRODUCT( (Region=K12) * ( * Sales ) )

Thanks a lot to Mike for such a nice tutorial!

[timemerick]

great tip. I usually add two sumifs together to achieve the same results. Your solution is much more elegant.

[Luciano_mp]

Mike, you're the guy. During those years I learned a lot with your videos, and I continue to learn. Thanks.
Mike, você é o cara. Durante esses anos aprendi muito com seus vídeos, e continuo a aprender.Obrigado.

[PedroCabraldaCamara]

Another magical video! Tks for being there Mike

[Blame_John]

Thank you thank you thank you!!! You are just awesome and you did help me a lot with my job! You have my gratitude! I have had so many questions answered via your videos!

[krn]

Great question and brilliant answer Mike. Thanks.

[entertainmentgalaxy]

Cool trick... I think real fun is to learn from

[VSP]

I appreciate so much what you are doing. Thank you again.

[eCabinetstipsandtricks]

Sumifs and sumproduct Great tip Mike. Thanks.

[itachili]

Thank you Sir. Really appreciate your work, I have learned so much from your amazing videos!

[robertlohman]

This would have been a fun "Duel" and I am guessing that Mr Excel would go for VBA.  You get the point lesson, thankyou.

[amitkumar-ipyv]

Mike, really good explanation. learnt a new trick.

[AGRInovaScience]

Hi ExcellsFun! Thanks for the great job you are doing to share your knowledge in Excel.
I really appreciate if you can help me out with one issue I am having at work.
I need to figure out a formula that gives me a date, depending on weekdays.
So, I have in one row the dates of the delivery of our orders, and next to that cell, there should be that date + 5 weekdays, which is our maximum period of time since we get the order till we ship the goods.
I really really appreciate your comments.
Thanks in advance.

[VS-rhrq]

sum with sumifs....did not know this..thank u

[thx]

please do it in spanish or tell to him in this case to the owner of channel of exel facil that he does this videos in spanish dear master !!
your videos are very very good !!
some parts I don't undertand duo to that I know more or less english and for that is the difficulty !!
please in spanish !!
take 100 points good man !!
all the videos that you publish right now are magestic and incredible !!!
excelent "
god bless you good man !!

[ericlefevre]

A variation of the formula in Magic Trick 1368 is to list the criteria in quotation marks inside curly brackets separated by commas. Then the sum function will work.

[mans]

Thank you for all the excel content, big fan. One question tho, what's the benefit of doing this vs a pivot table?

[vaz.felipe]

Hey Mike, how are you doing?! Thanks for this amazing video! Please, I'd like to ask you if do you know a way to show in a matrix all possible combinations between two or more variables. I know how to use COMBIN or PERMUT to see the number of combinations, but build the matrix not! Thanks in advance!

[SalaamBaltistan]

Great Sir
can you please tell me the name of screen capture software that you are using

[porter]

First, thank you for creating all the content you do!  I have a workbook I am launching at work, and want to create a "how to use" video for users.  QUESTION:  Which screen capture software do you use to produce your videos?