excelhelp.in Archives - Page 2 of 11

Dec202020 by excelhelp13No Comments

Sumproduct To Sum Monthwise 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.

Dec202020 by excelhelp13No Comments

Count And Sum With Sumproduct In Excel

FORMULAS, Lookup & Reference, Maths

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. To refer individually please check this post SUMPRODUCT In EXCEL. IN THIS EXAMPLE :- In this example we will count the specific search term Shoes in a range and only sum those values if they are available. To do this we will use 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.

Dec192020 by excelhelp13No Comments

SUMPRODUCT In Excel

FORMULAS, Maths, Statistical

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 Exampl...

Dec192020 by excelhelp13No Comments

SUMIF And INDIRECT To Sum On Multiple Sheet

FORMULAS, Lookup & Reference, Maths, Statistical

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&"!$...

Dec192020 by excelhelp13No Comments

Vlookup And Indirect On Multiple Sheets

FORMULAS, Lookup & Reference

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 t...

Dec182020 by excelhelp13No Comments

Convert Text To Date Format In Excel

Date And Time, FORMULAS, Text

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.

Dec182020 by excelhelp13No Comments

Vlookup Not Working In Excel

FORMULAS, Lookup & Reference

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.

Dec162020 by excelhelp13No Comments

Filter In Excel

Home Menu, Lookup & Reference

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 Deta...

Dec122020 by excelhelp13No Comments

SUMIF Time In Excel

Date And Time, FORMULAS

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.

Dec112020 by excelhelp13No Comments

Goto Special In Excel

Home Menu, Menu Bar

DEFINITION:- The Go To Special Function In Excel Allows You To Quickly Select All Cells That Meet Certain Criteria, Such As Cells Containing: Comments, Constants, Formulas (Numbers, Text, Logical, Errors), Blank Cells, Objects Etc. Blank Will Select The Blank Cell Only.Current Region Will Select From The Active Cell To Last Row. makes Sure You Don't Have A Blank Cell Otherwise It Will Consider Above The Blank Row As Last Row.Current Array Will Select The Active Range Belongs To.Object Will Select Any Object Available In A Range. Ex:- ShapesLast Cell Will Select The Last Used Cell In A Worksheet. Even If You Delete The Data From Cell.Visible Cells Will Select Only Visible Cell If Any Filter Is Applied Or Even Any Row Or Columns Are Available.Conditional Formatting Will Select The Forma...