If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Thursday, July 11, 2013

Using COUNTIFS (Array function)

Using COUNTIFS (Array function)

We can use the COUNTIFS function for conditional counting and for multiple criteria. In below example, COUNTIFS function with array formula is used. To make sure that we have the right results with us, we will combine the SUM function as well in it.

Example!

From the below data, we want to count the Documents that Eric worked on and the Document are either ‘Press Release’ or ‘Prospectus’ and they are processed in the Month of January.

COUNTIFS function is simple, we first define the range and then the criteria related to that range, then next range and its criteria and so on.

Since one of our criteria has multiple conditions i.e. type is ‘Press Release’ or ‘Prospectus’ so we will write it in {} and will also add the SUM function in the beginning and use array function. If it was just one condition then we wouldn’t have required it. To make it array, press CTRL+SHIFT+ENTER.

See the formula below for our example data!


=SUM(COUNTIFS(E:E,C20,F:F,"January",H:H,{"Press Release","Prospectus"}))


Wednesday, July 10, 2013

Some General but Useful Excel Learning

Excel Shortcuts
Convert Date number to Date format
Removing the space from beginning and end of a cell value
Arranging Raw Data in an organized format
Create and name a table
Using VLOOKUP function
Pivot Table
Condition formatting to highlight duplicate values
To Get VLOOKUP + 1 value




1-      ALT+ES
Paste special > Values

2-      ALT+EE
Find & Replace

3-      ALT+DPP
Pivot Table & Pivot chart Wizard

3.1- CTRL+PageUp or Page Down
To toggle between sheets of a workbook

4-      Formula to convert the date number to date format
=TEXT(A1,"dddd d, mmmm")







5-      While you copy data from some source and find that a value in a cell have a space in the beginning or the end, please use the below formula to remove it.

=TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))

6-      For arranging the raw data in a format that you might require

Store your raw data in Sheet1

Create a new sheet (say, Sheet 2) for capturing the result


Name Cell A1 and B1 in Sheet2 as Data and Year respectively

In Cell A2, pls input

=OFFSET(Sheet1!A1,ROW(Sheet1!A1)-1,0)

In Cell B2, pls input
=OFFSET(Sheet1!A1,ROW(Sheet1!A1),0)

To copy the above 2 formulas to the other cells in Col A and Col B of Sheet2



Raw Data




Arranged Data







7-      To create and name a table
Select the data and press CTRL+L and then ok. Under the Table Tools on Design Tab, you will see the default table name which you can change by simply typing there the new name and pressing enter. To view the design tab/Table Tools, your cursor should be somewhere in the table.






8-      VLOOKUP
VLOOKUP locates the value in the first column of a table and returns a value in the same row from another column in the table array.
Example
We have the below table with us in the sheet of a Workbook namely sheet3.




In the sheet 2 we have a list of KDs and now for those KDs we want to pull the respective ticker. So we will use the VLOOKUP function for same.

=VLOOKUP(A2,Sheet3!A:D,2,FALSE)

In this example formula the A2 is the reference of KD or lookup value that we want to look in the table which is in sheet3 so after putting the comma we will select that table, and then after a comma, we will put the number of column in which our required data is. So in this example the table has in total 4 columns A:D and our required data is in the second column so we will put 2 in the formula. Then after the comma we will type False or 0 which is to make sure that the values we are looking for which in our case is KD, is exactly matching. Press entry and you will find the required result there.




9-      Creating Pivot Table
Pivot Tables in Excel are used for summarizing analyzing, exploring and presenting our data.
It can be used for summarizing data like finding Avg., Count or Sum of some value like Salary, Sales, or Time for each department, designation wise and with a filter of office.
 Creating Pivot Table an example

We have the below raw data with us and now we want to calculate the Total Salary, designation wise for each department along total salary (all designations.) of each dept. The best way is to do that through a Pivot table.

Emp Name
Hire Month
Designation
Salary
Dept
Age
Chris Siedman
Feb-08
AM
15000
B&T
25
Mary Elizabeth
Apr-08
Analyst
13500
M&C
28
Scott Zunic
Jan-08
S. Analyst
14500
Insurance
31
Robert Clark
Feb-08
Manager
25000
M&A
23
Mathi Ramki
Mar-08
AM
16000
CDS
24
Michael Wegener
Apr-08
Analyst
10000
B&T
22
Aaron Teitelbaum
Feb-08
S. Analyst
13500
M&C
25
Conor McDonnell
Mar-08
Manager
28000
Insurance
28
Henry Dickson
Apr-08
Manager
26500
M&A
31
Scott Sedlak
Feb-08
AM
13000
CDS
23
Brendan McKinley
Feb-08
Analyst
10000
CDS
24
Scott Cottrell
Jan-08
S. Analyst
12000
M&A
22
Matthew Schultheis
Jan-08
Manager
26000
CDS
25
Zeynep Leffler
Mar-08
Manager
26500
B&T
28
Roger Ashworth
Apr-08
AM
13500
M&C
31

Steps

Ø  Select the Data –Go to  Insert Ribbon-PivotTable
Ø  A window will open, now chose where you want the Pivot report to be placed, New Worksheet or existing, if existing, specify the location by providing cell reference and then press OK.
Ø  On your left hand you will see the Pivot Table Field List, If not showing then just click anywhere in the blank Pivot table, it will start showing.
Ø  Now select the Row & Column Labels, here in our case it would be Dept. as row and Designation as Column.  Drag the fields from the list in the respective section i.e. Column or Row.
Ø  In the values section, select the field for which you want calculated results, in our case it would be salary. You will see the label there as ‘Sum of Salary’. In case if you want these numbers to be Avg. or Count, just click on the Drop down and the Value Field settings, there you can change the Sum to Avg. or whatever you want.
Ø  Report Filter is the section where you can select a field for which you would be able to filter your overall results. Like in this case we can select the Filter as ‘Age’ for understanding purpose. The default filer would be ‘ALL’; we can change it to any age value.
Now see in the below snapshot, how organized our raw data is looking in the Pivot Table. Just practice it yourself and then you will feel the importance of it more





   10- Conditional Formatting to highlight duplicate values
Through conditional formatting, we can easily identify the duplicate number or text values by highlighting them.
Simply select the data I.e. the column(s) and go to Home > Conditional Formatting and then Highlight Duplicates and Press ok, you will see the duplicate values highlighted, if any.




    11- To Get VLOOKUP + 1 value
We can use the Index+Match function to find the value that is below the value that VLOOKUP finds.
The formula would be like =Index (value’s column, Match (search value, search column, 0) +1)
For below example in the snapshot the formula would be =INDEX(A1:A10,MATCH(A12,B1:B10,0)+1)