How to use the SUMIFS function in Excel

Welcome to Excel 3 Tip Friday from DataMinded, where you can keep your Excel skills fresh each week by easily learning a new tip, a shortcut and a function.

This week we cover how to remove case sensitivity in VBA code (advanced), how to use the Escape key effectively in Excel, and how to use the SUMIFS function to sum a range based on multiple criteria.

1.png

How to remove case sensitivity in VBA with Option Compare Text

When you compare text in VBA you might have noticed that it is case sensitive. So if you are looking for the word “Dog” in a list, and your list contains the word “dog”, you will see that it will not find a match. 

This is useful in many occasions but there are scenarios where you just want to be able to find a specific text without worrying if its upper or lower case. 

In VBA we can do this by using the Option Compare Text statement. 

We want to compare the text in cell B2 and C2 and write TRUE in cell D2 if they are equal and FALSE if they are not. 

Let’s try and compare the text in two cells:

1.png
 

Open your editor and write the following code:

2.png

Run this code and you will notice that in cell D2 you will have FALSE. As far as VBA is concerned this two words are different.

3.png





Now let’s add this line to the top of our module:

Option Compare Text

4.png

Rerun the code and you will see that in cell D2 we will have TRUE displayed now. We have removed the case sensitivity from our code. 

5.png

Using the Escape Key / ESC shortcut in Excel:

There is a few uses for the ESC shortcut in excel:

  • You can exit full screen mode , just press ESC and you will be back to a normal view window

  • You can use it to remove the entry to a cell. If you start writing something in a cell or enter a formula and press ESC, all the entry will be removed.

  • You can also use ESCto get out of a menu or window. Try and open the Data validation dialogue box for example then press ESC, you will see that the window will be closed.

To get a full list of Excel shortcuts, check out the Microsoft help article


How to use the SUMIFS function to sum a range based on multiple criteria:

The SUMIFS function in excel is used to find the sum of a range that meets one or more criteria.

The syntax for this function is as follows:

6.png
 

sum_range – this is the range that we want to sum

criteria_range1 – this is the first range that to be checked if the first criteria is met

criteria1- this is the first criteria that we want to check

criteria_range2 – this is the second range where we will check for our second criteria

criteria2- this is the second criteria that our data needs to meet to be summed

Only the first criteria range and criteria are mandatory, the rest is optional and you can have as many as you need. 

In our example below we want to sum all the UK sales for the product Top:

7.png






sum_range - C2:C11

criteria_range1 – A2:A11

criteria1-“UK”

criteria_range2 – B2:B11

criteria2-“Top”

This formula will return 307. 

Please note, the criteria ranges and the sum range must have the same number of columns and rows otherwise the formula will give you an error.

To read more about this function please click here

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit