Excel Conditional Summing Tricks

This video shows how to do basic conditional summing with SUMIFS, where you want to add values in the amount column where the status is open. We then add a trick that enables you to analyze multiple conditions using OR logic. And a final trick to enable you to use a wildcard partial match for values that begin with subtotal.
- conditional summing basics with SUMIFS
- how to enable OR logic with SUMIFS
- how to do a partial match for begins with logic

Chapters in this video:
00:00 - Introduction
00:19 - Conditional Summing
00:49 - OR Logic
02:28 - Wildcard

Also, check out these videos with time saving Excel Hacks:

[PaulEBrownbill]

thanks, great use of a wildcard for future use. Paul

[yankoyankov]

Thank you so muuch . I was looking for exactly that and your video made it to my feed . Have a wonderful day filed with joy and excitement .

[jerrydellasala]

Glad you showed the wildcard option. Rather than having to do two sumifs for Open + Pending, that could have been
=SUMIFS(C12:C20, D12:D20, "*pen*")
Note that with text it's NOT case sensitive! Also, using array notation, the formula
=SUMIFS(C12:C20, D12:D20, {"Open", "Pending"})
returns those two values, but in two cells in a row (changing the comma between Open and Pending would return the values in a column. While it's not elegant, the formula
=SUM(SUMIFS(C12:C20, D12:D20, {"Open", "Pending"}))
also works.
I really like these short videos you do, and have gone through a lot of your others. Would love to see more longer videos WITH SAMPLE FILE(S).

[jeffolefson]

You could also have used sum if for both of these two conditions. But you make a good point. Sumifs is universal it can do sumif type conditions and multiple conditional or sumifs.

[HARwinsOUTagain]

ExcelU catchphrase: “So what are we supposed to do…do it manually?” “No!!!” 😎

[micheltremblay]

It would be possible with this for OR SUMIFS =SUM(SUMIFS(C12:C20, D12:D20, {"Open", "Pending"}))

[johnboffin]

Hello Sir, I am building a spreadsheet with 7 columns and 1200 rows. Column A is just a reference column, B is dates column which are fine, column C is with Validation drop down codes list, column D is details with drop down, tested and working all down to 1200 rows. Column E is payments F is Income and G is balance (Balance column work as it should) now I need your input in column E and F, If I select from column C for Income code it should only allow me the income column and if selected payment from C it should allow me to enter in payments column. In income column I have 5 source of income and payments I have about 44 or it may grow prior (My code list and details list grows as it should). I need help using validation (Custom) in these two columns E & F, so that the entry don't get switched between two. Thanks a lot if you could help.. (I use Excel 2000)

[mohamedmeshref]

Why did not you put an excel sheet to try your ideas on it?