Maths Archives - Page 2 of 3

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

Sumproduct And CountIf To Find Missing Value

FORMULAS, Maths

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

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

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

Dec122020 by excelhelp13No Comments

SUMIF And DATE Function In Excel To Sum Between 2 Dates

Date And Time, FORMULAS, Maths

DEFINITION:- SUMIF And DATE Function Together Can Work Dynamically WITH The Date Range To Find The Total Amount - Price For A Given Criteria. It Is Really Helpful To Figure Out The Total Amount Of Sales / Purchase In For The Date Range From A Large Database. For More Details Please Check The Image And Video Below. Base Formula :- SUMIF(date_range, ">=" & start_date,sum_range)

Dec122020 by excelhelp13No Comments

SUMIFS And DATE Function In Excel

Date And Time, FORMULAS, Maths

DEFINITION:- SUMIFS And DATE Function Together Can Work Dynamically Between The Date Ranges To Find The Total Amount - Price For A Given Criteria. It Is Really Helpful To Figure Out The Total Amount Of Sales / Purchase In Between The Date Range From A Large Database. For More Details Please Check The Image And Video Below. Base Formula :- SUMIFS(sum_range, dates,">=" & start_date, dates, "<=" & end_date)

Nov302020 by excelhelp13No Comments

Rank Function In Excel

Maths, Statistical

Open Excel File.Select The Cell Where you Want To The Result To Get Populated.Then Add This Formula In Particular Cell =RANK(10,A1:A10)There Is Third Optional Parameter (Ascending / Descending ) =RANK(10,A1:A10, 1 'For Ascending Order') or =RANK(10,A1:A10, 0 For Descending Order)Make Sure To Lock The Range By $ Sign.Descending Order Is By Default Set Optional Parameter.That's it :)

Apr72020 by excelhelp13No Comments

SUM AVERAGE MAX & MIN Function In Excel.

FORMULAS, Maths

For using these 4 function in excel and to know the actual purpose of all those function. Please follow the step mentioned below. SUM () function add all the numeric value available in different range. SUM() function only need the reference range For Example:- =SUM(B2:C7)AVERAGE () function add all the numeric value available in different range and divide it by the counting the cell with numeric value available. Suppose there are 4 cell and its total is 100 then Average () function will divide 100 by 4 100/4 = 25 is the average. For Example:- =AVERAGE(B2:C7)MAX() function find the bigger number from the given range or a cell reference. Suppose there are 4 cell and its contain 10, 20, 30, 40, then MAX() function will give you the 40 as a max number. For Example:- =MAX(B2:C7)MIN() functio...

Apr42020 by excelhelp13No Comments

NESTED SUMIF With Drop Down List

FORMULAS, Maths, Text

Create a Drop Down List and then apply SUMIF formula in next cell. Just follow the below step to create drop down menu and then apply the Sumif formula First we need to create a drop down list.Select a cell where you want to have your drop down list.Go to Data TabGo to Data Validation and click on it and one dialog box will pop up.Select List from that dialog box and go down one step into source row in that dialog box.Now select the range for the data you want to create a drop down list. For Ex:- A2:A10Once Selected just hit ok button in the dialog box and your drop down list is prepared.Then in the next cell apply the NESTED SUMIF formula.For Example see the step shown in the image and a video provided below.