SUMIF function to Summarize data

This function works like a simple Sum function. But you can add a criteria to it. It is very useful when you want to find out sum of values for certain items from the huge list of items. As we go ahead I will take you through the formula syntax and example. Also, if we use SUMIF and COUNTIF(which is another  similar function in MS Excel) together than we can calculate average based on criteria. In my next post I will describe COUNTIF.

Formula: =SUMIF(RangeOfThingsToBeExamined ,CriteriaToBeMatched ,RangeOfValuesToTotal)

Here is example below which show baby day-wise baby product sales.

Date	Item	Quantity	Cost	Amount
17-Mar-09	Dolls	44	148	6512
12-Jan-09	Hairpin	8	57	456
15-Apr-08	Diapers	8	176	1408
28-Jun-08	Baby Oil	29	124	3596
24-Nov-09	Dolls	43	120	5160
16-Oct-09	Diapers	34	159	5406
24-Mar-09	Baby Oil	39	196	7644
16-Jan-09	Diapers	26	154	4004
17-Apr-08	Dolls	28	84	2352
4-Jul-08	Baby Oil	39	82	3198
28-Nov-09	Diapers	36	101	3636
20-Oct-09	Cap	30	117	3510
3-Apr-09	Dolls	17	122	2074
23-Jan-09	Cap	45	55	2475
25-Apr-08	Dolls	6	93	558
				51989

The task here is to summarize report item-wise and bifurcate total in two part, part one where quantity is sold more than 25 and other part is where quantity is less or equal to 25. There are many ways to achieve this task. But here we will try SUMIF function to achieve it. Another way is PIVOT which I will soon post as another post.

Table below shows part one where we want to summarize the sales table item-wise.

Items	Amount total	Formula
Dolls	16656	=SUMIF(B2:B16,C19,E2:E16)
Hairpin	456	=SUMIF(B3:B17,C20,E3:E17)
Diapers	14454	=SUMIF(B4:B18,C21,E4:E18)
Baby Oil	14438	=SUMIF(B5:B19,C22,E5:E19)
Cap	5985	=SUMIF(B6:B20,C23,E6:E20)

Range part of SUMIF covers B2:B16 examines which cells has Item Dolls, B22 has Doll which it takes as Criteria, SumRange which is from E2:E16 add up all the respective cell in amount column which has "Dolls" in B column

Table below shows part two our task where we are summarize amount total where quantity greater than 25 or less than equal to 25

Criteria	Total	Formula
Total of Amount for Quatity >25	47493	=SUMIF(C2:C16,">25",E2:E16)
Total of Amount for Quatity <=25	4496	=SUMIF(C3:C17,"<=25",E3:E17)
	51989

Range part of SUMIF covers C2:C16 examines quantity column which cells has number greater than 25. Here Criteria is a string ">25", SumRange which is from E2:E16 add up all the respective cell where quantity is greater than 25.

I am sure this post is helpful in understanding SUMIF function. Post your comments

If you have any further doubt with SUMIF, please download example of SUMIF function or you can write me. I assure you prompt response.

SUMIF Example download

If you are first time visitor, please subscribe via email to receive updates, add-ins, e-books and lot more.

We assure you knowledge, not SPAM!

Read more on this article...

Days360 to find number of days

This function returns number of days between two dates. Only difference between Days360 and Datedif is Days360 consider year as 360 days and 12 months of 30 days each. Days360 is useful in accounting systems where year needs to be considered as 360 days.

Formula: DAYS360(StartDate, EndDate, TrueorFalse)

True: Enables the formula for European account systems.

False: Enables the formula for USA account systems.

Take a look at the example in the table below. Copy-Paste the table starting from A1

Start Date	End Date	Formula	Results
1-Jan-2009	7-Jan-2009	=DAYS360(A1,B1,True)	6
1-Jan-2009	1-Feb-2009	=DAYS360(A2,B2,True)	30
1-Jan-2009	31-Mar-2009	=DAYS360(A3,B3,True)	89
1-Jan-2009	1-Dec-2009	=DAYS360(A4,B4,True)	359

If you take a look at each row you will find that the formula does not include last day. Like 1-Jan-2009 and 7-Jan-2009 has 7 days between them. But formula returns 6. Hence, to correct this we will add 1 to formula.

=DAYS360(A1,B1,True) +1

Currently, I am posting basic functions of MS Excel. To receive updates on email, please enter your email in to Subscribe to Free MS Excel help.

We assure knowledge, no spam.

Read more on this article...

Auto Sum using shortcut

This method will reduce the time you take to write long sum formula. During my experience about using MS Excel, Sum is most common formula I used across most of MS Excel jobs. I prefer to either record a macro or find shortcut for the most repeated task. This not only saves time but also increase efficiency.

Shortcut key: Alt + =

Month	2008	2009	Total
January	2241	2214	Press Alt + = key here
February	2124	2356
March	2780	2476
Total	Press Alt + = key on this cell

When you press Alt + = key at the end of column you will get sum of column and Alt + = key at the end of row will give you sum of row as shown in example.

Note: This auto sum feature works only on the columns and rows where data is continuous without any empty cells in between.

Subscribe here, its free.

We assure you knowledge, not SPAM!

 

Read more on this article...

Vlookup function to find if value is present in range

I am little known for my MS Excel knowledge which I thought of sharing around. For beginners, Vlookup is biggest challenge and personally I have ample of people who ask me to teach Vlookup. Here is post to help all beginners with Vlookup formula, my all best wishes are with you. Approach will be in following manner. I will first explain you the formula syntax followed by example. To make best out of this post, I would suggest you to copy-paste the example in a MS Excel sheet. 

Formula: VLOOKUP(Valueyouwanttofind, Rangefromwhichyouwantofind, Columnnumber, SortedorUnsorted)

Valuesyouwanttofind are the customer id listed in column 1 of table below.

CustomerID	CustomerExistorNot	Result
50043	=VLOOKUP(A2,Sheet2!$A$1:$C$20,1,0)	50043
50048	=VLOOKUP(A3,Sheet2!$A$1:$C$20,1,0)	50048
40325	=VLOOKUP(A4,Sheet2!$A$1:$C$20,1,0)	#N/A

 Rangefromwhichyouwantofind are the customersid listed in column1 of table below. Since we are working to find out if the values existst or not hence we will choose columnnumber as 1 which will return you the customerid from table below and will return #N/A if the customer do not exist in table 2.

CustomerID	CustomerName	City
50041	Sharma K	Mumbai
50047	Chauhan M	Delhi
50048	Ritu K	Kanpur
40023	Khan L	Vashi
45601	Verma K	Belapur
50023	Areeb	Mumbra
40215	Riyaz A	Mumbai
41280	Dadan A	Kharghar
40326	Amit D	Belapur
50023	Faheem A	Mumbra
40215	Riyaz A	Mumbai
40351	Rodriquez P	Mumbai
42892	Raheem A	Kolkata
50043	Keith S	Mumbai
40052	Mahadevan S	Mumbai
40234	Ganguly D	Mumbai
40021	Smith M	Delhi
40020	Chakorborthy M	Vashi
40019	Raju C	Delhi

Copy-Paste both table in Sheet1 and Sheet2 starting from A1 cell.

SortedorUnsorted is the last parameter which can be true/false or 1/0. I would suggest you to use 0 if you are not sure whether the data is sorted(indexed) or not.

The most important things you should remember when you use Vlookup is Rangefromwhichyouwantofind should have Valuesyouwanttofind values listed as the left most columns. Like in the above scenario, you may have columns before customerid column but your range for Rangefromwhichyouwantofind should start with customerid. Also, use absolute reference in Rangefromwhichyouwantofind.

Do write us your view about this post. We will provide you something better that would help you understand better.  Enclosed below is link from Official Microsoft site explaining Vlookup function.

http://support.microsoft.com/kb/181213

Also, take a look at video tutorial design to help you with Vlookup.

 

If you are new/first time visitor, do subscribe via email to receive latest updates, e-books, free add-ins and lot more.

We assure you knowledge, not SPAM!

Read more on this article...

Find Absolute value using ABS function

In this post we will understand a very basic function ABS which returns value/magnitude. Like for ABS(-9) will return 9, ABS(10) will return 10 and so on. Table mentioned below is example which will help you in understanding the ABS function. Also, we will discuss one application of this formula. I would appreciate if you can spend some time write comment and do let us know if you have any issues. We are here to help.

Copy paste the table starting from A1 cell of your MS Excel sheet.

Number	Absolute Value	Formula
25	25	=ABS(A2)
-62	62	=ABS(A3)
-3.5	3.5	=ABS(A4)
3.5	3.5	=ABS(A5)

Formula:
=ABS(Address Of Cell)

Examples:
In the example below, we are describing a model of target-actual table. Fifth column shows the percentage of actual exceeded/shortfall. Table 1 will illustrate calculation without ABS function while table 2 will describe using ABS function.


Difference for 7-Mar-06 is negative as actual has exceeded target and hence the percentage appear as negative. No matter whether you have achieved or not but percentage cannot be negative.

Table 1

Date	Target	Actual	Difference	Exceeded/Shortfall Percentage
1-Mar-06	40	37	3	7.50%
4-Mar-06	54	54	0	0.00%
7-Mar-06	60	65	-5	-8.33%
			Target - Actual

Table 2

Date	Target	Actual	Difference	Exceeded/Shortfall Percentage
1-Mar-06	40	37	3	7.50%
4-Mar-06	54	54	0	0.00%
7-Mar-06	60	65	5	8.33%
			ABS(Target - Actual)

Kindly post your comments and subscribe to this blog via email to receive latest updates, excel tips, e-books and free add-ins. Click here to Subscribe

We assure you knowledge, not SPAM!

Read more on this article...

Calculate End of Month using EOMONTH function

You may not have seen many people using this formula. However, it is as useful as SUM formula at times. During this post, I will guide you about syntax of formula and its uses. Like the title of post says, it is used to find end of month. I will share some very important scenario's and method to find beginning month using same formula.

The function return's end of month and it requires the input as Date. Also, since it return's the date as number you will have to format cell to show dates.

Copy-Paste the table in MS Excel starting from A1.

Date	Month	Formula	Returns
6-Mar-09	-1	=EOMONTH(G11,H11)	28-Feb-09
6-Mar-09	1	=EOMONTH(G12,H12)	30-Apr-09
6-Mar-09	2	=EOMONTH(G13,H13)	31-May-09

Formula: =EOMONTH(StartDate,Months)

In the above example, -1 with 6-Mar-09 will return you the end of Feb. Similarly, 0 will return current month and 1 will return next month.

Example1: You want to find the number of days remaining in current month.

Formula: =EOMONTH(TODAY(),0)-TODAY()

TODAY() will return the current date and zero will tell EOMONTH to consider current month. Difference between EOMONTH(TODAY(),0) and TODAY() will return number of days left in month.

Example2: You want to find the first of current month.

Formula: =EOMONTH(TODAY(),-1)+1

TODAY() will return the current date and -1 will tell EOMONTH to consider previous month. Adding 1 to end of previous month will return 1st of current month.

Kindly let me know your views about post. Subscribe via email to blog to receive latest updates in your inbox.

We assure you knowledge, not SPAM!

Read more on this article...

Datedif function

Datedif is function to find difference in dates. Days360 function also find the difference in days. But the major difference between Day360 and Datedif is Days360 consider 12 months each of 30 days hence its not very accurate. But, this is useful if you have an accounting system which require 12 months each of 30 days. Like in other post I will help you understand the syntax of formula followed by examples.

Formula: Datedif(PastDate, CurrentDate, "INTERVAL")

Interval: Interval can be days, Months, Years, Yearsdays, Yearmonths and monthdays. Mentioned below is description for each type of interval.

Past Date	Current Date	Interval	Formula	Result
24-Nov-03	10-May-08	days	=DATEDIF(A2,B2,"d")	1629
24-Nov-03	10-May-08	months	=DATEDIF(A3,B3,"m")	53
24-Nov-03	10-May-08	years	=DATEDIF(A4,B4,"y")	4
24-Nov-03	10-May-08	yeardays	=DATEDIF(A5,B5,"yd")	75
24-Nov-03	10-May-08	yearmonths	=DATEDIF(A6,B6,"ym")	5
24-Nov-03	10-May-08	monthdays	=DATEDIF(A7,B7,"md")	17

Copy-Paste the table in MS Excel starting from A1 Cell.

I have attempted to give example in another post. I am enclosing the link below. Request you to visit same.

Datedif example

Kindly post your view about post in comments. Also, you can Subscribe to Free MS Excel help by Email to receive latest update about this blog and we will also send free add-ins, e-books and lot right in your inbox.

We assure you knowledge, not SPAM!

Read more on this article...

Concatenate function to join text

Concatenate is one of most useful function is Microsoft Excel. It is used to join content from two cells. Here we will discuss examples of CONCATENATE function, example and alternative method to joined cells.  Let’s take an example, that we have five columns with Address, area, city, State and pin code and we want to join five columns to get complete address in one column.

For example, A14 - Address, B14 - Area,

C14- City, D14- State and E14- Pincode. Then, the formula to concatenate will be as mentioned below

=CONCATENATE(A14, " ",B14," ",C14," ",D14," ",E14)

Another way to achieve same will be by using formula

=A14&" "&B14&" "&C14&" "&" "&D14&" "&" "&D14

Click on image below to see concatenate example in details.

If you like this post, do post your views as comments. Also, you can subscribe via email to receive updates, MS Excel tricks, e-books and add-ins.

We assure you knowledge, not SPAM!

Read more on this article...

Install add-ins

Add-ins are basically xla(MS Office 2003) and xlam(MS Office  2007) with built in user define functions or macros.  Add-ins can be created by recording macro or writing macro/function in MS Excel workbook and save the workbook using Save as option.  When you click on save as option, you will find xla option under ‘Save as type’ drop down.  Once you click on xla or xlam option you will find that this file automatically saves in an add-in folder which is located under following path.

C:\Documents and Settings\<>\Application Data\Microsoft\AddIns

So, in case if you receive or download any add-ins then you should follow the steps below to install add-ins on your computer.  For example I have enclosed the add-ins n2t which has function to convert numeric to text (123 – One Hundred and twenty three)

For MS Office 2003

1. Copy the add-in in the following folder
   
2. C:\Documents and Settings\<>\Application Data\Microsoft\AddIns
   
3. Open  MS Excel, click on tools and click on add-ins 
   
4. Check the box adjacent to N2t.
   
5. After you install follow the steps to check if add-ins installed properly.
   
6. Type =n2t(A1) in the B1 cell and type 123 in A1. N2t will return  One Hundred and twenty three
   

For MS Office 2007

1. Copy the add-in in the following folder
2. C:\Documents and Settings\<>\Application Data\Microsoft\AddIns
   
3. Click on MS Office icon on the top right hand side corner. 
   
4. Click on Excel options button
   
5. Select the add-ins  and click on Go button at the bottom
   
6. Check the box adjacent to N2t.
   
7. After you install follow the steps to check if add-ins installed properly.
   
8. Type =n2t(A1) in the B1 cell and type 123 in A1. N2t will return  One Hundred and twenty three
   

For practice, please click on link below to download sample add-in. 

Click here to download sample add-ins

Read more on this article...

How to open xlsx file in MS Office 2003 or earlier version

The new version of MS Excel which is Microsoft Excel 2007 saves the file in xlsx format. If you have MS Excel 2003 than you won’t be able to open these files. Hence, you need Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats.

Enclosed below is link to download file convertor.

FileFormatConverters

For more information about the Compatibility Pack, see Knowledge Base article 924074 on Official Microsoft website.

I am sure this will definitely resolve your problem of opening files saved MS Office 2007 version. If you find this information useful, please leave your comments/feedback.

Note: We are not associated with Microsoft in any manner. This blog is just an effort to make people realize the potential of Microsoft Excel and share my knowledge

Please do let me know your comments about my attempt to help for people struggling with xlsx files.  Also, you can subscribe via email to receive updates

We assure you knowledge, not SPAM!

Read more on this article...