SumIF Function in Excel

SumIF Function in Excel

SumIF is one of the basic functions in Microsoft Excel to sum cells that meet a single criteria. It is a built-in function in Excel, which helps to sum cells based on dates, numbers, and texts having specific criteria.

SumIF is a worksheet function that is entered in a worksheet cell as part of a formula. SUMIF supports logical operators that include (>,<,<>,=) and special characters (*,?).  click here for more details.

Purpose

Sum numbers in a range meeting supplied criteria.

Return Value

Supplies sum value.

Syntax

Similar to all other formulas of Excel, its syntax starts with “=”

The syntax of SUMIF function looks like this-

=SUMIF(range, criteria, [sum_range])

Arguments  

Range – The range of cells that you are looking to implement the criteria against. 

Criteria – It is used to determine which cells to add.

Sum_Range – Optional. It is used to add cells together. If sum_range is excluded, the cells in range are added together instead.  for further info, visit :
https://ittraining.iu.edu/training/browse.aspx?group=EXCEL

SumIF Function in Excel

Usage notes 

When sum_range is excluded, the cells in range will be added together.

All the text criteria, or criteria that has math symbols, should be enclosed in double quotation marks (“).

On the other hand, numeric criteria does not need any quotation marks to be supplied.

The special characters such as “?”, “*” can also be used in criteria.

To located an exact question mark or asterisk, you can use a tilde (~) in the beginning of question mark or asterisk (i.e. ~?, ~*).

Use of SumIF Function

By using SumIF formula in Excel, we are able to compute the sum of multiple data depending on desired conditions. As the name suggests, this function, sums-up the data when the exact criteria is met. This function has 3 arguments that includes range of data, criteria, and sum_range. We have to select all these 3 arguments to find out the desired results. 

How to Use SumIf in Excel? 

Let’s understand SUMIF function with an example. We have created the following worksheet that has customer records with fields like, Names, Items, Price and Paid?. 

In this example, we want to see the total amount paid by customers and the total amount they still have to pay. For this, we will have to add two more labels to this worksheet i.e. Total Amount Received, and On Credit.

Now our spreadsheet is ready to write SUMIF formula to find Paid Amount, that provides the sum of the amount which has been paid by the customers.

Syntax:

SUMIF(range, criteria, sum_range)

Formula:

=SUMIF(D2:D11, “Yes”, C2:C11)

The formula, D2:D11 holds keywords “Yes” and “No”, which describes whether the customer has paid the amount or they still have to pay, C2:C11 holds the price of different products.

When we will apply this formula, it will display the total amount paid by the customers by finding ‘Yes’ keyword in Paid? column and will add value in the Price Column. It will evaluate that 774$ has been paid, as given in the screen shot below.

Let’s apply same formula to check the amount customers still need to pay. For this, we require little change in this formula. Now this function will search “No” in paid? Column and will sump up comparing values in Price column. 

Use the formula as given below

=SUMIF(D2:D11, “No”, C2:C11)

It will show sum of amount which is still on credit as you can see in the snapshot given below.…

How to Create a Report in Excel With Formulas

How to Create a Report in Excel With Formulas

In this article we will be looking at how to create a report with Excel using INDEX, MATCH and SUMIF.

Let’s assume that we have a Liqueur firm that has two reports that have been downloaded from another application, Microsoft Nav. click here for further details.

The first file is a Posted memo file

The second file is a master file with customer information

To analyse this data

  • On the Posted Memo file; Right-click on the B column and select Insert to insert a new column
  • To add the sales person name to the file we will use the index/match formula

=INDEX(customers!A2:A3123, MATCH(memos!A2,customers!B2:B3123))

The index is what you want to add to the formula, from the customer sheet all the salespeople that are listed.

The match is what you are going to match from both sheets. Select the first Acct# that is listed on the posted memo sheet, and then select all of the customer numbers that are listed on the customer sheet. for further detail, visit : http://guides.library.upenn.edu/excel

How to Create a Report in Excel With Formulas

  • Press enter and then double click on the fill handle to populate the entire column
  • Enter the sales people’s names in a separate column from the table.
  • Use the sumif Function to total each of the individual sales

=SUMIF($B$2:$B$445,K2,$J$2:$J$445)

Select the column where the criteria exists, (sales persons) – these must be absolutely referenced – click the F4 key on each column reference.

Select the individual criteria, (Larry)

Select the column that you would like added (the Amount)– these must be absolutely referenced – click the F4 key on each column reference.

  • Drag the formula down to complete all the sales for the Sales People
  • Select the data in our newly created table and click the insert tab
  • Select the Pie chart, from the Charts section
  • Select the 2D Pie and your chart will be created
  • Click on “Chart Title” and enter the desired text.
  • You will now have a clear visualization of you data
What is VLOOKUP and how to use it

What is VLOOKUP and how to use it?

Description

VLOOKUP in Excel is a function that performs vertical lookup and searches for specific values in the first column of the excel sheet. It returns the value for the same row for the position in index_number.

An easy example would be – having a list of fruits along with their pricing. Now, if you want to find out the price of a particular fruit using this function then you would use VLOOKUP. click here for further details.

The VLOOKUP feature in Excel in a built-in one and falls under the category of Looku or Reference function.One can also use it as worksheet function. As a WS function, VLOOKUP is entered as part of the formula in a worksheet cell.

This function is really easy to understand once you figure out how exactly it works. Read this tutorial to know more about it.

How to work with it

Like any other formula, this one will also start with an equal (=) sign. You can use the formula in any blank cell of the sheet. So, it should look something like this –

=VLOOOK(

Adding the Arguments

The purpose of the argument is to tell the function(VLOOKUP) what and where to search for a particular value.

Name of the item being searched is going to be the first argument. IN this case, the first argument will be Banana. The argument will need to be in double quotes since it’s a text. Therefore, it should look like this. for more details, visit : https://www.collin.edu/ce/courses/excelI.html

What is VLOOKUP and how to use it

=VLOOKUP(“Banana”)

The second argument will have to be the cell range containing the data. So, in this case, the data is in A2:B16. You’ll need to use ‘comma’ to separate each argument.

=VLOOKUP(“Banana”, A2:B16

The third argument is going to be the column index number. Since, we’re trying to find the price of the fruit, which is in the second column, therefore our third argument, will be ‘2’ which is the column number.

=VLOOKUP(“Banana”, A2:B16, 2

The fourth argument is either TRUE or FALSE. TRUE argument will look for approximate matches within the given range. This is typically used when there are sorted numerical values in the first column. But, since we are looking for exact matches, we will use FALSEargument. After this, we will close it with parenthesis.

=VLOOKUP(“Banana”, A2:B16, 2, FALSE)

Press enter and youwill get the answer like this –

Special Note – Remember that this function always searches the first column in the given range.So, in the current example it will start off by searching through Column A for the Banana.…

How to Make a Pie Chart in Excel

How to Make a Pie Chart in Excel

A pie chart is a circle graph in Excel that is parted into different slices. Each slice of pie chart defines a proportion of the whole. This graphical object is used to show the percentage of the data in the chart.

Uses of a Pie Chart

Pie chart is an excellent tool in Excel that helps to create an attractive visualization of complex numbers. You can use it to create proposals, your annual sales forecast, and much more. However, we suggest not to use Pie chart for data having more than 6 categories. Since, the large numbers of categories may look bad in a single chart. It may make chart difficult to interpret and understand. click here for more details.

Microsoft Excel gives you a range of options to create pie charts including 2D and 3D pie charts that come with a variety of designs and types.

Pie Chart is usually created to present data that can be classified into nominal or ordinal categories and shows percentage. At present, Pie chart is one the most loved and accepted formats to present data in a visual form.

Steps to Make a Pie Chart in Excel

Microsoft Excel is an excellent tool to create a pie chart in just a few clicks. Here are steps, following them you can learn making pie charts in no time.

Step 1: Type Data into an MS Excel Sheet-

First, type your list of data in Excel in two columns, which is quite easy. Type your description of the numbers in column 1st and your numbers in column 2nd. As in the following example, the column A contains the categories of animals and column B has numbers associated with these animal categories. Make sure you are not leaving any blank rows or columns while typing the data. for related info, visit : https://www.ischool.utexas.edu/technology/tutorials/office/excel/

How to Make a Pie Chart in Excel

Step 2

Select the entire entered data by clicking on the top of your data and pull the mouse pointer to the bottom right.

Step 3

Now go to “Insert Menu” of your worksheet and click on “Pie” and then select the type of pie chart you are looking for. Now click on chart icon you have chosen; Excel will immediately insert pie chart into your excel worksheet.

Tip: 

In case you have made a mistake while typing your data in Excel, you don’t need to create the whole chart again. Microsoft Excel gives you the advantage; when you make a correction in the original data, your pie chart automatically gets corrected.…

What is Excel

What is Excel

Sometime before 1993, Lotus 1-2-3 used to be the industry standard for the spreadsheet application. But later that year, tables turned when Microsoft Corporation introduced the modern version of Microsoft Excel. This propriety commercial application which is part of the extended Microsoft Office Suite family became an instant hit and quickly replaced Lotus-1-2-3 as the preferred Spreadsheet software at business places. To date, it has become essential to possess Microsoft Excel usage skills to work in any office or complete some personal spreadsheet projects. for related info, click here.

Microsoft Excel possesses basic spreadsheet features of organizing its workspace in a grid of cells that are arranged in numbered columns and rows which are used to organize data and manipulate arithmetic operations. It comes with a variety of functions and formulas that can be used to quickly perform some statistical, financial and engineering calculations. It can also prepare a pictorial representation of your data using histograms, line graphs, charts and some limited 3D graphical displays. for more about excel, click on  : http://www.nobles.edu/student-life/excel.cfm

What is Excel

To use Microsoft Excel, you need to ensure that any latest version of Microsoft Office suite is installed on your computer. For Windows 7 Ultimate and other older versions, click on the start button then programs. On programs menu, click on Microsoft Office and select Microsoft Excel from the list. For Windows 8.1 and Windows 10, click on windows logo at the bottom left corner followed by a click on the down arrow at the bottom. Proceed to the Microsoft Office group from where you can launch Microsoft Excel.…