COUNTIF & SUMIF

Normally we use these functions often while analyzing the data. Let us see the practical use of these functions.

COUNTIF - COUNT function counts numbers in a range, while COUNTIF function counts values that is provided in the function as a condition, e.g. If you want to view count of available numbers in a range, then use COUNT function but sometimes we would like to know how many times a value is appeared in a column. Let us say in column A, Salesmen's names are appearing, we want to see how many times single salesman name is appeared in that column. Then we will use countif function. i.e. =COUNTIF(A:A,A1) , It works as follows, in A1 salesman name is "BOB" and we are looking for how many times his name is appearing in column A (or in any other column). COUNTIF function returns number which shows how many times BOB is appearing in column A. Apply filter if you would like to see salesmen appearing more than once in a list. The same thing can be done by using pivot table.

SUMIF - SUMIF is function used for doing sum based on condition. This can be used when you would like to sum based on a condition usually for a summary report. Let us see with an example.
Suppose you would like to see total of sales of a product in a summary report. Data includes sales figure by diffrent cities, e.g.













Now to create summary report, to see the sum of total sales by city. List the city names of which summary report you would like to create. e.g.







Now insert following formula in sales column and drag it till Row 4.
=SUMIF(B:C,G2,C:C) . It will first ask range range to be used for doing sum. Here I have selected range B:C i.e. City & Sales column from first table, then condition is supplied i.e. city name in G2, and finally range is given on which sum needs to be done. so formula will look city name from G2 in range B:C and sums the numbers from Sales column from first table which matches the city name and returns the total sum viz.







Again this can be also done by using pivot table.

Splitting Name/Data in a cell

Sometimes we have to split data in a different cell. e.g. We have Names in column A. And we have to split names as First Name, Middle Name &  Surname. There are two ways to do this activity.

1.  Select the column in which Names are available,  Click on “Data” menu, then click on “Text to column” . Select “File Type” as Delimited. Click “Next” , Select delimiter as “Other” and In a text box Next to other , enter space.  Click Next and then Finish. Data will be distributed in 3 columns.

2. Using Formula -

If Name is in A1 then formula to get

First Name

If Name in column A is “Ajay Kumar Pai” Then Above formula will show first Name only i.e. “Ajay”.  LEFT function returns leftmost characters of a cell, and FIND function return number where space is given in Name. so in above case. FIND function finds the space starting from first character. As first space is after Ajay then it returns number as 5, Then LEFT function returns first 5 leftmost characters i.e. “Ajay”

Last Name 

Above formula first substitutes space after first name as # then finds location of space after middle name as first space is replaced by #. After that it counts the lenght of characters first name and middle name using function LEN and finally it counts total characters and deducts it from length of characters till first space i.e. after middle name. It gives the number of characters in last name and Right function splits rightmost characters upto number of characters of last name and gives last name.

if Middle Name is not in the name i.e.  only first and last name is there like “Ajay Pai”. then another formula is used. i.e.

Middle Name -

It works same as above explanations.

~ Sangram

Excel 2010 features

Microsoft describes features of Excel in the following link. This is really a good article to read. Powerpivot is the one of the key feature in Excel 2010.

http://office.microsoft.com/en-us/excel/excel-2010-features-and-benefits-HA101806958.aspx

Start Date of A Week

I had to create a report depicting no. of issues arrived weekly. Each day issues were reported. Based on date I had to find the start date of a week. Here is the formula to find start date of a week.

Suppose Date is in cell “B1″ then formula to find start date of week is =IF(WEEKDAY(B1,2)=1,B1,B1-(WEEKDAY(B1,2)-1))after posting formula if you see date in number format then right click-> select “Format Cells” -> select “Date”. It will display start date of a week for selected date.

In above case “Monday” is considered as start date of a week, if you want to choose other day as start day of a week then change the number in weekday function i.e. “Weekday(B1,1)” or “Weekday(B1,2)” or “Weekday(B1,3)”. 1 is assigned for Sunday, 2 is assigned for Monday and 3 is assigned for Tuesday. After 3 function does not accept return type.

~ Sangram

Date-to-number-or-text-conversion

Today I faced one problem. I wanted to convert Date from dd/mm/yyyy date format to text dd/mm/yyyy format.

If the date is 1st ; then it should be converted into 01.

If the month is 4th ; then it should be converted into 04.

I had written following formula for conversion:-

It is assumed that B9 field DATE.

=CONCATENATE(IF(LEN(LEFT(DAY(B9),2)=1),

CONCATENATE("0",LEFT(DAY(B9),2)),LEFT(DAY(B9),2)),"/",

IF(LEN(LEFT(MONTH(B9),2)=1),CONCATENATE("0",LEFT(MONTH(B9),2)),

LEFT(MONTH(B9),2)),"/",YEAR(B9))

but; there is some alternative easy way to do this using Text function

~ Harshal