Business Problem:

Below table lists orders of various products sales for the period between March and July in Microsoft Excel. Our objective is to calculate the total revenue from Wireless Headphones and Flame Lamps sold between May and July using SUMIFS formula

 

Challenge

You would think you can logically use SUMIFS this way (since syntax is valid), but this version of the formula doesn’t produce results we are looking for:

=SUMIFS(R6:R17,P6:P17,”Wireless Headphones”,P6:P17,”Flame Lamp”,Q6:Q17,”>4/30/2019″,Q6:Q17,”<8/1/2019″) 

 

Solution

Below are the 2 ways how SUMIFS formula can be used to solve the business problem:

=SUMIFS(R6:R17,P6:P17,”Wireless Headphones”,Q6:Q17,”>4/30/2019″,Q6:Q17,”<8/1/2019″)+SUMIFS(R6:R17,P6:P17,”Flame Lamp”,Q6:Q17,”>4/30/2019″,Q6:Q17,”<8/1/2019″)

=SUMIFS(R6:R17,P6:P17,”Wireless Headphones”,P6:P17,”Flame Lamp”,Q6:Q17,”>4/30/2019″,Q6:Q17,”<8/1/2019″)

 

Watch Video Tutorial on how to solve the challenge

How can you solve multiple Criteria logic challenge (OR vs AND) in EXCEL SUMIFS function. Watch below video tutorial to learn more. And make sure to download work files to try it yourself:

 

Download Free Excel Assessment Test Work Files

There are 2 Excel Assessment work files included in this free download:

• SUMIFS.Formula.Multiple.Conditions.and.OR.Logic.xlsx – Use this file to follow along with the video and solve challenges yourself
• SUMIFS.Formula.Multiple.Conditions.and.OR.Logic.xlsx.zip – Zip archive containing the work files

Complete below form to download Free Excel Assessment Practice Files to follow along with the video. After information is entered click Download button and you will be redirected the page, where you would be able to complete the downloads: