Скачать или смотреть Excel - Conditional Sum: Adding Multiple Conditions Using OR Functions - Episode 661

Microsoft Excel Tutorial: Conditional Sum: Adding Multiple Conditions Using OR Functions.

Welcome back to the MrExcel netcast! In today's episode, we have a question from Jeff in Burlington, Massachusetts about conditional summing in Excel. As you may know, the SUMIF and COUNTIF functions in Excel 2003 and earlier versions could only handle one condition. So, for more complex scenarios, we had to resort to using array formulas or the SUMPRODUCT function. Jeff is using the SUMPRODUCT version and we'll be taking a closer look at his formula in this video.

Let's break down Jeff's formula. It's a big, long formula that may not even fit on your screen. Essentially, Jeff is using the SUMPRODUCT function to look at everything in Column D and see if it's equal to "Verizon". Then, he multiplies that by everything in Column B to see if it's equal to product "XYZ". Finally, he uses the corresponding value from Column F if both conditions are met. When we hit Ctrl+Shift+Enter, we get a conditional sum of 165727. We can test this by changing one of the records to "Verizon" and seeing the result change.

But what if Jeff needs to add more conditions using "or"? In his formula, the multiplication sign between the two conditions acts as an "and" operator, meaning both conditions have to be true. To use "or", we can simply replace the multiplication sign with a plus sign. This means that if one condition is true or the other condition is true, the corresponding value from Column F will be used. You can continue to add more conditions using parentheses and the plus sign to create complex conditional sums.

Jeff, you can also use another multiplier to add another condition, such as *(B2:B564="XYZ"). Just remember to use the right parentheses to avoid any errors. Thank you, Jeff, for sending in your question. In tomorrow's episode, we'll be looking at the same formula but using the SUMPRODUCT function instead. So make sure to tune in for that.

Thank you for stopping by the MrExcel netcast. We hope this video was helpful in understanding conditional summing in Excel. Don't forget to subscribe to our channel for more Excel tips and tricks. See you tomorrow for another netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Jeff from MA writes with todays question; creating a conditional sum array formula that uses OR functions instead of AND functions. Episode 661 shows you how.

Table of Contents:
(00:00) Complicated conditional summing
(00:26) Limitations of SUMIF and COUNTIF
(00:49) Adding multiple conditions with OR operator
(02:33) Combining multiple conditions
(02:53) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Adding another multiplier to the formula
Adding multiple OR conditions using the plus sign
Building complex conditional sums with parentheses
Combining multiple AND and OR conditions
Conditional summing in Excel 2003
Example of conditional summing using Verizon and product XYZ
Result of the conditional SUM formula
SUMIF and COUNTIF functions
Syntax of the SUMPRODUCT formula
Using array formulas for multiple conditions
Using multiple levels of parentheses
Using SUMPRODUCT for conditional summing

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...