DEFINITION:- To Sum the cell only which contains formula we can use Sumproduct and Isformula function together to achieve it. To know more about Sumproduct please check this post - SUMPRODUCT IN EXCEL
DEFINITION:- In this example we will use Sumproduct and Month Function to Sum the value based on month selection. Sumproduct return sum for a single range and Month function returns month from a given range. To do this we will also take help of hyphens or you can say a 2 minus sign (--) before giving the range reference. It actually convert and check the condition with true and false and return 0 for false and 1 for true. For more detail please check below step with image and video.
DEFINITION: The Sumproduct Function Multiplies Ranges Or Arrays Together And Returns The Sum Of Products. The Countif function count the number of values occurs in a range or array. Trim function removes the un necessary space from a cell.
IN THIS EXAMPLE :- In this example we will use 3 function Sumproduct - Countif - Trim to compare the two database in a range and will find the missing value if they are not available. To refer individually please check this post SUMPRODUCT In EXCEL. / COUNTIF IN EXCEL / TRIM FUNCTION.
To do this we will use help of hyphens or you can say a 2 minus sign (--) before giving the range reference. It actually convert and check the condition with true and false and return 0 for false and 1 for true. For more detail please check below step with image an...
DEFINITION:- The Sumproduct Function Multiplies Ranges Or Arrays Together And Returns The Sum Of Products. The main purpose of Sumproduct is to multiply the array or range and then add those value and return the sum of the particulars.
SYNTAX:- =SUMPRODUCT (array1, array2, ...)
EXAMPLE:-Suppose You Have A Few Items In Stock As Per The Image Below. If You Want To Know How Much The Total Price According To The Quantity, You Should Use The Sumproduct Formula.
Explanation: The Sumproduct Function Performs This Calculation: Quantity * Price (2 * 20000) + (3* 15000) + (5 * 12000) + (4 * 9000) + (5*500) = 183500
VALIDATION:- The Ranges Must Have The Same Dimensions Or Excel Will Display The #Value! Error.
SINGLE ARRAY:- If You Supply A Single Range Example...
SUMIF function adds all numeric numbers in a given range of cells based on one criteria. Suppose there is name in column A and amount in column B and in column A if a name is repeating more than 1 time and you want to add all the amount It have next to column then Sumif is a best formula to make your work easy.
DEFINITON INDIRECT:- INDIRECT function is used in Excel to change over a content string into a legitimate range reference. For example: if you want to lookup what value is available in cell A2 then simply use =INDIRECT(“a2”). Indirect use text reference and actually convert it to reference. So here “a2” is converted to cell(a2).
NOTE: - Make sure to change the reference according to your range (database).
FORMULA IN EXAMPLE : =SUMIF(INDIRECT(D2&"!$A...
DEFINITON VLOOKUP:- Vlookup is vertical' lookup built-in Excel function. VLOOKUP is used when you need to find something in a table or a range by row. For example to look up a item price of an any company part by its item code number, or to find an employee name based on their ID.
To Separately Viewing Please Visit This Posts VLOOKUP IN EXCEL. INDIRECT IN EXCEL
DEFINITON INDIRECT:- INDIRECT function is used in Excel to change over a content string into a legitimate range reference. For example: if you want to lookup what value is available in cell A2 then simply use =INDIRECT("a2"). Indirect use text reference and actually convert it to reference. So here "a2" is converted to cell(a2).
In This Example :- We will lookup the Shoes value with Vlookup and Indirect function in all th...
DEFINITION:- Many times we come across the situation where Dates are in text format and not in Date format and even if you change the format to date still its not converted to date format and not working as accepted. So Its only indicate that excel is not recognizing it as dates and because of it it is not working accordingly. But to make sure that excel recognize it as a date we will follow the step as shown below.
Select The Range Of Dates Cells.Go To Data Tab.Click On Text To ColumnsHit Next Until It reach to Step 3.In Step 3 You Will Find Date Option.Select The Date Option.Now Select The Correct Format From The Dropdown next To it.Then Hit Finish.That's It :)For More Details Please Check Images And Video Below.
DEFINITION:- Many times we come across the situation where V-lookup or other formulas not working as accepted and showing error #N/A even the number format is correct for the number values. Its only indicate that excel is not recognizing it as numbers and because of it V-lookup is not working according to this example. But to make sure that excel recognize it as a number we will follow the step as shown below.
Select The Range Of Numbers Data.
Go To Data Tab.
Click On Text To Columns
Hit Next Until It each to Step 3.
Then Hit Finish.
That's It :)
For More Details Please Check Images And Video Below.
DEFINITION:- Filter is a great way to select the data of your choice by applying to a particular database. Filter has many option available for a different criteria and with the help of those option we can extract the data according to our choice. Filter can be applicable on more then one columns. For more details please follow the step below.
Open Excel File.First Select The Database To Apply The Filer.Go To Home Tab.Click On Sort & Filet On The Right Side Under Editing Command Tab.Once Clicked You Will See Drop Down List.Click On Filter Option.Once Clicked The Filer Will Get Applied On Database.Go To The Particular Column For Which You Want The Filter To Look in.Select The Criteria And Apply The Filter.Once Applied Only The Selected Criteria Item Will Be Displayed.For More Detai...
DEFINITION :- SUMIF is a inbuilt function in excel which sum up the multiple values in a proper range with proper given criteria. TIME function is also a inbuilt function which used to measure the time or display the time in excel. In this example we will use SUMIF function to sump up the time values from a different cell on a particular criteria.
To know more about sumif and sumifs please check this post SUMIF IN EXCEL & Also SUMIFS IN EXCEL
NOTE:- Make sure you have time format set for the output cell. please check the image below.
