Faysal Easy Excel

How to SUM by matching partial text in Excel In this tutorial, I'm going to share another tips on how to sum by matching partial text in Excel . First of all, Microsoft Excel has a function called, "SUMIFS". Also has SUMIF. But I'll work here with "SUMIFS". This function allows us to sum a specific range. Based on some specific conditions. The situation of your condition is different in real life. As a result, I'm writing how to sum by matching partial text in excel tutorial. How to sum by matching partial text example:   if you want to SUM only the BD based name shown in the list below, what will you do?  Image 1:  How to SUM by matching partial text in Excel Well, in this case You can do by following below steps: So, as you can see, I have to check all cells and find if the string “BD” appears in the cell or not, and if it does, I need to include the value in the adjacent cell in column B while doing the sum. The...

This function is used to test a value weather it is LOGICAL or ILLOGICAL. If this function founds a value as LOGICAL according to Microsoft Excel then it returns TRUE otherwise it returns FALSE. The Syntax: =ISLOGICAL(value) Example: I'm trying to write an IF formula that will take the logic from 3 different cells A2, B2 and C2 cell, where A2 = 13, B2 = and operator "<" (Less Than) and C2 = 51. If I type =IF(A2 Image 1: Trying to write a formula from this values Now here are some examples: Image 2: ISLOGICAL() Examples

Sometimes we need to calculate the Loan Amount in Excel to borrow loan from Bank or some others company. There are many Banks and Financial Companies offered many kinds of Schemes. But before choosing anyone of them, you can simply calculate these scheme through Excel with few built-in functions, which will give you the real scenario. But before starting calculation you need to understand few Financial Terms. Some Financial Terms: PV (Present Value): This amount is basic. No interest or Tax included here. It is also known as Principal Value. FV (Future value): Based on Time Value of Money (TVM), it calculated for a specific period of time. It can be greater than PV or less than PV. Rate (Interest Rate %): It is the interest the set by the Bank. The Bank will take this interest as additional amount from your principal amount. Bank will mention it in contract as Annually. Make it sure from the Bank first. Assume that, Bank said Interest Rate is 12% Annually. Now you can con...

Frequency() function is used to find the occurred number of a given data group from a single data set. It is an Array formula like Transpose(). You can easily understand Frequency function if you do know Statistics, where Frequency counts based on a group or class from a raw data. I'll try to explain in details about this function here. Function Structure: The structure of this Frequency function is: =FREQUENCY(data_array,bins_array) There are 2 main arguments in this function. These are: 1) data_array: This is the raw data range. You need to select an un-grouped raw data range here for count. 2) bins_array: This is the range of your own created Class or Group by which the function will count the data_array. Example 1: Assume that, A2:A16 is the record of Rainfall in Dhaka City. B2:B11 is the Class based on which you wish to group the A2:A16 data. D2:E12 is the reporting area where you need to use Frequency function. D2:D12 is your reporting group. If you imagine...

The CELL function used to return a specific information of a Excel Cell which you need to know. This function requires a cell address like A1, B1:D6 etc. Function Structure: The structure of this function is: =CELL(info_type,[reference]) There are 2 parts of this function. These are: (a) info_type: You need to mention here, which information you would like to know. Like, Color, Address, etc (b) [reference]: The cell that the information is to be returned for. If you apply [reference] then you will get the mentioned cell's information. But if you omitted it, then this CELL function will return the information of that cell where the =CELL() used. Example: Image 1: Cell function in details with example

This function is available in: MS Excel 2007 and above version, MS Excel 2011 for Mac and above version, Android, iPhone, Online etc. There are 7 popular lookup functions in MS Excel. One of them is Choose(). This function returns a value from a list (maximum 254 items in a list) whose nth number has given. This function requires a position number and a list of range. Then the function will return the value from list of range by using the mentioned position number. Functions Structure: The structure of this function is: =CHOOSE(index_num,value1,value2,value3,......,value254) or =CHOOSE(index_num,list_range) There are 2 main arguments or parts in this function. These are: (a) index_num: Need to mention a position number 1 to 254 to return that position's value. (b) value1: This is the 1st item name of list. And this could be maximum value254. You can use a range as list_range also. Examples: Assume that, below is sample table B3:C8 and you are reque...

HLookup function (Horizontal Lookup) is commonly used in MS Excel to get values from a data table by matching a value in lookup row and return the value from the matched column. The match type can be define as Exactly match or Approximate match. It works like an English T shape. It matches a value in a row and returned value from another row of same column where value matched. Function Structure: The structure of this function is: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) There are 4 arguments or parameters in this function. The first 3 arguments or parameters are essential. That means if you want to use HLookup function, then you must mention the first 3 parameters. And the last parameter is optional. By default the last parameter is set to True (Approximate match). But for a better result you need to set the 4th parameter. All of these parameters are briefed here: lookup_value: Need to mention which value to lookup. table_array: Range of data tab...

The Lookup function is used for finding a specific result from a single row or a single column, based on a lookup_value with same range of single row or single column. It does not allow 2 or more rows or columns range to find the lookup_value or returned value like others lookup function VLookup, HLookup do. Lookup Function: The structure of this function is: =LOOKUP( lookup_value , lookup_vector , result_vector ) lookup_value:   The value which you want to look up in a specific row or column range. lookup_vector: It is a single row or single column range that contains the values to be looked up. These values must be in ascending order. That means before using this Lookup you must sort the lookup_vector range as Ascending order. Descending order will return the incorrect value. result_vector: It is the single row or single column range from which you want to return the value. The range size need to keep same as lookup_vector. Example: Table B4:D7 is a poin...

Rank function used for ranking a value among a range and return a ranking number. The ranking number which will return, depends on which option you would like to mention. There are 2 options. For largest to smallest (0), and another one is smallest to largest (1). Rank Function: The structure of this function is: =Rank(number,ref,[order]) Function Details: There are 3 arguments in this function. First 2 is essential and last one is optional. These are described as below: number: It is mandatory. This is a single number, which you would like to find what is the position of this number. ref: Reference is another mandatory part. It refers a cell range. The number will find the position comparing this ref range. [order]: It is optional. You can mention 0 or 1 if you wish. Or else it will select 0 (zero) by default which means: Large number is number 1. If you mention 1, then this will work as smallest number is number 1. Example # 1: Below table A1:B5 shows the salary of the footb...

This Vlookup function is also known as Vertical Lookup . It is commonly used in Excel to match a lookup value from a column, and returned value from another column where lookup value matched. The match type can be define as Exactly Match or Approximate Match (Ascending Order). It works like an English L shape as I think. This function has a lot of uses. It supports Array, so that you can customize the lookup values. Vlookup Function: VLookup function is a lookup function that used in Microsoft Excel for returning a specific data from a data table by matching a cell value (Number, Date, Text, etc.). More clearly, when you need to add an information of each record of a table from source table, by matching each cell value of a specific range with source data table, then you need to use VLookup function. At present in Bangladesh , many company asks about VLookup in their Job Interview . Why? Because they feel that, if you do not have knowledge about VLookup (which is on...

Subtotal() is basically used to get instant summary of a filtered data table. It is used with the support of AutoFilter tool or can be used alone. If you use AutoFilter for Subtotal then all hidden rows become visible. But if you do not use AutoFilter then hidden rows becomes hidden. Subtotal Function: The Subtotal function structure is: =SUBTOTAL(function_number,ref1) Function Details: There are 2 main parts of this function. These are: function_number: Subtotal function can calculate many things like, SUM, COUNT, AVERAGE etc. Each of these function are stored with a number in Subtotal(). What you need to do is mention the Number first from below list: Image 1: SUBTOTAL() number chart ref1: This is nothing but a Range. You can Select a single cell or few cells selected randomly, or a range of cells. Example: Based on below data table assume that, you need to know the Salary of Mr. A in D4 cell. Image 2: Sample data table for Subtotal In D4 ce...