Excel - Sum With IF Conditions - Either SUMIF or SUMIFS - Episode 2580

Microsoft Excel Tutorial: Excel Conditional Sum: Multiple Criteria, Dragging Formulas, and More.

🎓 Dive into the world of conditional SUM in Excel with MrExcel! In this video, we unpack 6 different scenarios to help you become a SUMIFS pro. Whether you're a novice or an Excel aficionado, these solutions will elevate your data analysis game.

📋 Topics Covered:

SUMIF - Single Criteria:
Learn the basics of conditional summing using the SUMIF function. Bill Jelen, aka MrExcel, demonstrates how to sum sales based on a single criterion, such as the sales rep's name.

SUMIFS - Multiple Criteria:
Explore the power of SUMIFS when dealing with multiple criteria. Bill breaks down the syntax and shows how to sum sales based on the sales rep's name and the product.

Handling Multiple Criteria in the Same Column:
Tackle the challenge of multiple criteria in the same column. Bill presents three solutions, including a FILTER approach and a throwback to a 30-year-old gem: the DSUM function.

Dragging SUMIFS Formulas:
Uncover the trick to dragging SUMIFS formulas with dynamic ranges. Bill shares a technique to add dollar signs selectively, ensuring the formula adjusts as you drag it to different cells.

Using Functions as Criteria in SUMIFS:
Learn how to incorporate functions as criteria in SUMIFS. Bill demonstrates examples like summing sales greater than the median or average.

Working with Date Ranges in SUMIFS:
Demystify date range criteria in SUMIFS. Bill covers scenarios like finding sales before a specific date or between two date ranges.

🔧 Formulas & Functions Demystified:
Bill Jelen simplifies complex formulas, making them accessible to Excel users of all levels. Follow along and enhance your Excel skills with practical tips and tricks.

Table of Contents
(0:00) Conditional Sum in Excel
(1:17) SUMIFS with multiple criteria
(2:09) SUMIFS with 2 criteria in same column
(5:33) How to Drag SUMIFS formula
(6:27) SUMIFS with Function in Criteria
(7:22) SUMIFS with Range of Dates
(7:52) Clicking Like really helps the algorithm

This video answers these common search terms:
how to add multiple sum ifs in excel
how to sumif by month excel
how to drag sumifs formula in excel
how to use functions as criteria in sumif excel
how to sum if number in excel ignore error
how to use sumif in excel for multiple columns
how to sumifs with multiple criteria in the same column in excel
how to sumif in excel with multiple column criteria
how to use excel sumifs with date ranges
how to use a sumifs with a drop down list in excel
how to do multiple sumifs in excel
how to sum multiple conditions in excel
how to sum multiple criteria in excel
how to sum multiple rows based on criteria in excel
how to add multiple sumifs in excel
how to use a multiple sum if in excel
how to sum up multiple columns with sumif in excel
how to do sum if on multiple colums in excel
how to sumif in excel with multiple criteria
how do you use sumifs with multiple criteria in excel?
how to use sumifs in excel for multiple criteria
how to use sumifs in excel with multiple criteria
excel - how to do a conditional sum
how do i do sum if on excel
how do you use excel sum if
how does excel sum if work
how to create a formula with sum and if in excel
how to do a sum if excel
how to do a sum if function in excel
how to do a sum if in excel
how to do a sum if in excell
how to do conditional sum in excel
how to do excel sum if
how to do if sum in excel
how to do sum if formua on excel
how to do sum if in excel
how to do sum if on excel
how to lookup a sum of values with two criterias excel
how to make sum if in excel
how to sum a column in excel with condition
how to sum cells in excel if
how to sum columns in excel based on criteria
how to sum if numbers in excel
how to sum in excel if
how to sum values based on criteria in excel
how to sum with two criteria excel
how to use excel if and sum functions together
how to use if and sum function in excel
how to use sum if formula excel
how to use sum if function excel
how to use sum if in excell
how to use sum ifs excel
how to use the if sum funcction in excel
how to use the if sum function in excel
how to use the sum if formula on excel
how to use a sumif in excel
sumifs excel how to use
what is sumif in excel
how to use the sumifs function in excel
how to use sumifs condition in excel
how to use a sumif function in excel
how to use sumif exceljet
how to use the sumif function on excel
how to use sumif() excel

#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

[roywilson]

Nice video, thanks for posting. Seeing you use DSUM brought back painful memories of my first time seeing it used in a sheet I had taken over, it confused the hell out of me until I found it in my trusty Excel book. In the situations where that kind of calculation is required I think my goto would be PowerQuery probably with a space on grid in the workbook to enter parameters to feed the query(ies). Keep up the good work, always look forward to your videos.

[GeertDelmulle]

Hey Mr. Excel, Thanks for the consistent treatment of this topic!
But what about formulas in the sum-range?
That doesn’t work in SUMIFS and the like. Why not?
BTW: we, the people —er, the users— don’t care that it says “range” and not “array”.
(When needing a conditional calculation inside of a LET, we always need to switch to using FILTER)
Or am I missing something?…

PS: yes, in PQ-M we don’t have that problem. :-)

[sheresalia]

Is SUMIF the right function for this scenario? I'm looking to calculate cells B1 and C1, but only if Cell A1 has specific text (There are 3 possible texts, and dependent on the text in cell A1, each it will calculate two different cells. I believe i will have to repeat the formula two more times to accommodate the calculations based on the text in cell A1.
This is what i have to start, but I'm getting an error. =SUMIF(A1, "specifictext", _SUM(B1)*(C1))
Corrections and suggestions very much appreciated!

[andyloring]

for multiple conditions in the same column I'd use Sumproduct:

[Itsartee]

I have an issue with the third condition on the "sumifs" function - I need to create a function to look at an expense category for a month, then a year and total it in a summary sheet against the respective months' column as a comparative table. Since there are multiple years, there is a problem, and the values result in zeros. It works well with two conditions and reflects the values, but the third condition results in zero values. My table should pick the year from a drop-down cell marked as a constant. =SUMIFS(Table1[Debit], Table1[Category], 'Annual Summary'!$B4, Table1[Month], 'Annual Summary'!$C$2, Table1[Year], 'Annual Summary'!$T$3) is the formula used. Can you help me in correcting the error?

[DaveH]

You could have used this for multiple criteria =SUM(SUMIFS(C5:C20, B2:B20, G7, A5:A20, G5:G6)), if you are using older versions of Excel and didn't want to use CSE, then replace SUM with SUMPRODUCT.

[KishoreKumar-ofsb]

I am using := SUM(SUMIFS(C5:C20, A5:A20, G5:G6, B5:B20, G7)) It give the same result

[KishoreKumar-ofsb]

How to Drag SUMIFS formula section I am using : SUMIFS(C5:C20, A5:A20, F13:F17, B5:B20, G13:G17), It gives the same result

[rollingthunder]

Why not just use sumifs and forget sumif?

[waitplanwp]

please upload the file with the formulas