Category "Excel-Formulas"

Do you count errors in Excel? You know, the error function. If so, then this video is for you! In this chapter, we will count errors in Excel using the same with helpers functions like IF Functions and COUNT Function. In this video, We will be going all-in for advanced calculation of count functions with helpers functions like ISERROR, IF Functions along with COUNT Function. Table Of Content: 00:00 Introduction In this section, we are understanding the criteria that occur in your worksheet when you incur a lot of errors in your cell and you want to count it for reference purposes. You will get the demo sheet attached to this video below to work with us or you can work on your own worksheet if you have incurred such excel errors. 01:14 Detect Errors with ISERROR Function ISERROR Function returns a boolean value and detects if there's an error in your mentioned cell or not, Let see the syntax of the ISERROR function. IsError ( expression ) As you can see in the above syntax, if there is an error occurs then it returns TRUE and FALSE when there are no errors. You will get to know more about this function while performing count errors with IF Functionality. 03:00 COUNT Errors Using COUNTIF Function There's also another way to do it, you can also count errors using the COUNTIF function. COUNTIF counts the number of cells that have a particular condition. =COUNTIF( range, criteria) The range is our sheet or more specifically cell where we want to count errors and the Criteria field holds the value TRUE if there are any errors in your mentioned content otherwise it returns FALSE. In this way, you can count the total number of excel errors that occur in the cell, do follow along with the video to know more. 03:52 IF Function IF function is a logical function that returns a logical output based on the criteria is being used. let's see the syntax of the IF Function; IF(logical_test,value_if_true,value_if_false) Logical_test is the condition that you are checking for, if it returns TRUE then IF function will return value_if_true and when data return FALSE to logical test in the formula, It'll count count_value. Now let's understand how we can use this function in our count errors worksheet? 04:46 COUNT Function COUNT Function simply returns a number of cells that contains text strings, numbers etc which are basically non-blank. to understand it further, let's see the argument of the same; COUNT(value1, [value2], ...) Where value_one, [value] is the range of cells that you want to count and count all the cells including text, numbers, etc. So far we have learned about different functions that we can use to count errors in Excel. 05:27 Conclusion Now, we have learnt different ways to count errors and wrap those errors using our default values using IF function and other methods using the COUNT function. If you have any questions in mind do let us know in the comment box.

In this video, I will show you how to count specific words that occur in a cell. This is different from counting cells because we can not just use the COUNT function and specify "count" or "cells." We need to look at each word individually and then add it up. To do this we will use the LEN and SUBSTITUTE functions. These two functions allow us to find out how many characters are in a text string; and if we want, we can replace them with something else too! Table Of Content: 00:00 Introduction 01:08 Adding Helper Value 02:22 Adding Length of the cell using LEN Function 03:10 Substituting Value with Blank Value using LEN & SUBSTITUTE Function 04:16 Calculating the number of John in Specific Cell 04:57 Complete formula to Count Specific Word Occurs in a Cell 07:32 Conclusion Count function has its own limitation as you cannot count specific words that occur in a cell. So here we are using Excel LEN and SUBSTITUTE functions to resolve the same. Let's take an example where we want to count a number of "John" in a specific cell. We will first add a helper value so that we can easily identify the location of John in the text string. This is done by simply adding an apostrophe (') at the beginning and end of the text string as shown below: ='John' Now, we can use the LEN function to find out how many characters are in this text string. The LEN function will return 11 as there are 11 characters including both apostrophes. Next, we will use the SUBSTITUTE function to replace all instances of John. So we are using two functions here, lets see the argument of both. =LEN(text) =SUBSTITUTE(text, search_term, replace_with) Now follow the video above to understand how to do the same.

This video will show you how to use the NOT EQUAL TO operator in Microsoft excel with helper functions like IF function, AVERAGE IF Function, and COUNTIF Function. NOT EQUAL TO is an operator denoted as "<>" which is being used to compare or check if one value is not equal to another value in your worksheet. It's very common for numerical or logical calculation in Microsoft Excel. As the name suggests "NOT EQUAL TO", If you have data and want to sort duplication of text strings or numbers then this comes very handy. Let's say if you want to compare A1 cell should not be equal to B1, then you can simply put the formula i.e. A1<>B1 in cell C1, and it will return boolean values i.e. True or False. Do follow the video using the following timeline for the video; Table Of Content: 00:00 Introduction 00:56 Not Equal to In Excel Basics 02:00 Not Equal to In Excel Using IF Function 06:24 Not Equal to In Excel Using COUNTIF Function 11:10 Not Equal to In Excel Using AVERAGEIF Function 06:50 Conclusion In this video, we have explained the basics of NOT EQUAL TO with basic examples along with logical functions like IF, AVERAGEIF, COUNTIF using 3 different and practical examples to practice for you. If you want to download the same excel sheet used in the video you can download using the link below.

Do you have two or more worksheet to work on? want to sum across multiple worksheet in your current one, then this video is for you. In this video, we will learn how to sum multiple worksheet using 2 ways to resolve the same. checkout the table of content below and watch the video above for the same. Table Of Content: 00:00 Introduction 01:14 Copy Formula in Excel 03:19 Fill Formula In Rows or Columns 04:18 Absolute Reference 06:16 Move a Formula 08:20 Bonus Tip 09:38 Conclusion