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.
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:
Open your editor and write the following code:
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.
Now let’s add this line to the top of our module:
Option Compare Text
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.
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.
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:
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:
sum_range - C2:C11
criteria_range1 – A2:A11
criteria_range2 – B2:B11
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.