Sumif With Common Word in Range

31

=SUMIF($B$3:$B$22,"=?*is*?",$D$4:$D$24)

It will sum all cells in the range $D$4:$D$24where the word "is" exist in the range $B$3:$B$22.


Enjoy It

IF

How the IF function works

The Excel IF function checks to see if a certain condition is true or false. If the condition is true, the function will do one thing, if the condition is false, the function will do something else.

The IF function we are using in this tutorial asks if the value in column A is greater than the value in column B. If it is, the IF function will place the statement "A is larger" in column D. If it is not, the IF function will place the statement "B is larger" in column D.

Our IF function will be entered into cell D1 and it looks like this:

=IF(A3 > B3,"A is larger","B is larger")

Note: the two text statements "A is larger" and "B is larger" are enclosed in quotations. In order to add text to an Excel IF Function, it must be enclosed in quotation marks.

About to Excel SUMIFS Formula

What is SUMIF?

We all know that SUMIF formula helps to findout the sum of all values meeting a condition. So, if you have data like below

and you want to find out how many Spit Bombs you sold (spit bombs, really?) then you can use SUMIF like this:
=SUMIF(B3:B16,"Spit Bomb",D3:D16)

But as you guess, SUMIF has a serious limitation. It can only process one condition at a time. So if you want to find out how many Spit Bombs are sold in North, you have to,

1. Drive down to North and meet the victims to take count of spit bombs sold
2. Use array formulas to SUM based on multiple conditions
3. Use SUMPRODUCT formula

While the first option takes a lot of time, the other two involve writing complex formulas.

So What is SUMIFS Formula?

SUMIFS is the plural version of SUMIF. Using SUMIFS you can find the sum of values in your data that meet multiple conditions.

So, to get the sum of all the spit bombs sold in North, we just write,

=SUMIFS(D3:D16,B3:B16,”Spit Bomb”,C3:C16,”North”)

How Does SUMIFS Formula work?

SUMIFS formula takes a range for summing the values and at least one criteria range and criteria. You can specify as many as 127 conditions for summing your data.

Imagine asking “how many spit bombs Hansolo sold in North region of Planet Naboo between long long ago and long ago that resulted in more than 25% profits?” and getting an instant answer.

The beauty of SUMIFS formula is that it works with wildcards too, just like its siblings – SUMIF and COUNTIF. So you can write formulas like,

=SUMIFS(D3:D16,B3:B16,”Spit Bomb”,C3:C16,”*th”) to get sum of spit bombs sold in North and South.

So what is the catch?

While SUMIFS seems like all butterflies and berries, there is a catch. It works only in Excel 2007 and above. But with Excel 2010 in the horizon, I have high hopes that finally everyone will be able to enjoy the SUMIFS formula.

Bonus:

Just like SUMIFS, there is COUNTIFS and AVERAGEIFS too in Excel 2007+. I am sure you already know what they mean.

Basic Excel

Getting Started with Excel

Excel is a spreadsheet program that allows you to store, organize, and analyze information. In this lesson, you will learn your way around the Excel 2010 environment, including the new Backstage view, which replaces the Microsoft Button menu from Excel 2007.

We will show you how to use and modify the Ribbon and the Quick Access Toolbar, and how to create new workbooks and open existing ones. After this lesson, you will be ready to get started on your first workbook