HOW TO USE COUNTIF IN EXCEL

HOW TO USE COUNTIF IN EXCEL

Excel COUNTIF function is best & so simple to use for situations when you want to count cells, based on a single condition. COUNTIF Function can be described as a formula that can be used for counting the number of cells that fulfill a particular condition, within a predefined range.

How to use COUNTIF in excel. Microsoft Excel defines COUNTIF as a formula that, “Counts the number of cells within a range that meet the given condition”.

This definition clearly explains that: COUNTIF Function is a better and sophisticated type of COUNT formula that gives you the control over, which cells you wish to count.

Syntax

=COUNTIF(range, criteria)

Arguments :-

  • range –  the range of cells which you want to count.

  • criteria –the criteria that must be evaluated against the range of cells for a cell to be counted.

countif1

This formula counts those cell which values are greater then 100.

The formula is  =COUNTIF(D2:D11,”>100″)

The result is 4. Because D2 to D11 four cells meet the condition which values are greater then 100. Hence this formula shows 4 in D12.

 

COUNT CHARACTER VALUE WITH COUNTIF FUNCTION


It’s possible and so easy to count character values in excel with COUNTIF function. The COUNTIF function is not case sensitive.

countif2

Here a simple COUNTIF formula which calculate the specific word or character in given range.

The formula is =COUNTIF(D3:D11,”MANAGER”)

 

The result is 2. Because D3 to D11 two cells meet the condition which values are manager. Hence this formula shows 2 in D11.

HOW TO USE MULTIPLE COUNTIF 


If you want to count multiple words from a column or row then you will have to use multiple COUNTIF function with different conditions.

The syntax is same :-

=COUNTIF(range,”criteria”)+COUNTIF(range,”criteria”)+COUNTIF(range,”criteria”)

=COUNTIF(C4:C12,”H.R”)+COUNTIF(C4:C12,”I.T”)+COUNTIF(C4:C12,”QUALITY”)

 

multicountif

The formula shows the result is 7. Because cell C4 to C12 the word “H.R” comes three times, “I.T” also comes three times and one time is “QUALITY”. Therefore 3+3+1=7

 

HOW TO USE COUNTIFS IN EXCEL


The COUNTIFS function Count cells that match multiple criteria.If you want to count based on multiple criteria, use COUNTIFS function.

Syntax

=COUNTIFS(range1, criteria1, [range2], [criteria2], …..)

Arguments :-

  • range –  the range of cells which you want to count.

  • criteria –the criteria that must be evaluated against the range of cells for a cell to be counted.

    countifs

=COUNTIFS(B3:B11,”ANKIT”,C3:C11,”I.T”)

The result is 2. Because the condition of formula met two times.

SHORTCUT KEYS IN EXCEL

SHORTCUT KEYS IN EXCEL

Shortcut keys excel is most useful for a user because shortcut keys save your working time and make your task much efficient. This tutorial tells you about some important shortcut keys of excel .

Formatting Text In Excel

Description

  • Ctrl + B= Bold toggle for selection

  • Ctrl + I= Italic toggle for selection

  • Ctrl + U= Underline toggle for selection

  • Ctrl + 5= Strike-through for selection

  • Ctrl + Shift + F= Change the font

  • Ctrl + Shift + P= Change the font size

  • Ctrl + Shift + 7= Apply outline borders

  • Alt + Enter= Wrap text in same cell

 

Description

  • Ctrl + N New file

  • Open file Ctrl + O

  • Ctrl + S Save file

  • Move between open workbooks Ctrl + F 6

  • Ctrl +  F 4 Close file

  • F 12 Save as

  • Ctrl + P Display the print menu

  • Ctrl + A Select whole spreadsheet

  • Ctrl + Space Select column

  • Shift + Space Select row

  • Ctrl + Z Undo last action

  • Ctrl + Y Redo last action

  • Alt + F 4 Exit Excel

    Navigating

  • Description

  • Tab= Move to next cell in row

  • Shift + Tab= Move to previous cell in row

  • Page Up= Up one screen

  • Page Down= Down one screen

  • Ctrl + Page Down= Move to next worksheet

  • Ctrl + Page Up= Move to previous worksheet

  • Ctrl + Home= Go to first cell in data region

  • Ctrl + End= Go to last cell in data region

    Formatting Cells

  • Description 

  • Ctrl + 1= Format cells

  • Ctrl + Shift + F= Select font

  • Ctrl + Shift + P= Select point size

  • Ctrl + Shift + 4= Format as currency

  • Ctrl + Shift + 5= Format as percentage

  • Ctrl + Shift + 1= Format as number

    Editing/Deleting Text

  • Description 

  • Delete one character to right = Delete

  • Delete one character to left = Backspace

  • Edit active cell = F 2

  • Cancel cell entry = Escape Key

  • Highlighting Cells

  • Description

  • Select entire worksheet = Ctrl + A

  • Select entire row = Shift + Space-bar

  • Select entire column = Ctrl + Space-bar

  • Manual select = Hold Shift + with Left, Right, Up, Down Arrow

Key  Display & Printing

Discription

Alt + W + F + F=Freeze Panes

Alt + W + Q=Zoom

Ctrl + Mouse Scroll Wheel=Zoom

Alt + P + S + P=Page Setup

Alt + P + R + S=Set Print Range to Selected Area

Ctrl + F 2=Print Preview

Alt + W + I=Page Break View

Alt + W + L=Normal View

Alt + W + VG=Toggle Grid-lines

  • Inserting Text Automatically

  • Description 

  • Auto sum a range of cells = Alt + “=” Equals Sign

  • Insert the date = Ctrl + ; (semi-colon)

  • Insert the time = Ctrl + Shift + ; (semi-colon)

  • Insert columns/rows = Ctrl + Shift + + (plus sign)

  • Insert a new worksheet = Shift + F 11

  • Misc

  • Description 

  • SHORTCUT KEYS IN EXCEL

  • Find text = Ctrl + F

  • Replace text dialog = Ctrl + H

  • Create a chart automatically on new sheet = F 11

  • Edit a cell comment = Shift + F 2

  • Move to Next Sheet = Ctrl + Page Down

  • Move to Prior Sheet = Ctrl + Page Up

  • Go to a cell command = Ctrl + G

  • Auto Sum= Alt + Equals(=)

  • Formula Mode = Equals Sign

  • Insert Date In Current Cell = Ctrl + ; (semicolon)

  • Insert Time In Current Cell = Ctrl + Shift + ; (semicolon)

  • Spell Check = F 7

  • Tab Backwards = Shift + Tab

  • Auto Filter Ctrl + Shift + A

  • Cell Hyperlink Alt + Shift + H

Filtering, Sorting & Validation

Shortcut keys in excel

Alt + A + SS=Sort Data

Alt + A + SA=Sort Ascending

Alt + A + SD=Sort Descending

Ctrl + Shift + L=Filter Data

Alt + A + Q=Advanced Data Filter

Right Mouse Button + E + V=Filter by Cell’s Properties

Alt + A + M=Remove Duplicates

Format Menu

SHORTCUT KEYS IN EXCEL

Ctrl + 1=Format Dialog

Ctrl + Alt + V=Paste Special

Ctrl + Alt + V + T=Paste Formats

Ctrl + Alt + V + V=Paste Values

Ctrl + Alt + V + F=Paste Formulas

Alt + H + FC=Font Color

Alt + H + H=Fill Color

Alt + H + B=Border Options

Alt + H + A + L / C / R=Align Left / Center/ Right

Alt + H + 6=Increase Indent

Alt + H + 5=Decrease Indent

Alt + H + 0=Increase Decimal Places

Alt + H + 9=Decrease Decimal Places

Ctrl + B=Bold

Ctrl + I=Italics

Ctrl + U=Underline

Ctrl + 5=Strike-through

Ctrl + Shift + &=Add Borders

Ctrl + Shift + –=Delete Borders

Shift + Ctrl + ~=General

Shift + Ctrl + !=Number

Shift + Ctrl + @=Time

Shift + Ctrl + #=Date

Shift + Ctrl + $=Currency

Shift + Ctrl + %=Percentage

Shift + Ctrl + ^=Scientific

=TEXT(Cell, Format)=Displays cell using custom format

Alt + H + O + I=Auto-Fit Col.

Alt + H + O + A=Auto-Fit Row

Alt + H + O + W=Column Width

Alt + H + O + H=Row Height

Alt + H + L + R=Conditional Formatting

Alt + H + T=Format as Table

 

 

HOW TO LEARN EXCEL

HOW TO LEARN EXCEL

Excel is a most powerful spreadsheet and data analysis application
These days, every job requires advance Excel skills. An organization wants these Excel skills –creating tables, format data, manipulate basic essential operations like(sum, subtraction, multiplication, division, average & summaries with filters and formulas, highlight data with conditional formatting, validation, charting , advance formulas and much more another thing which you will learn from here.

HOW TO BECOME AN EXCEL EXPERT
Excel is a huge application in itself with thousand of features and formulas.Formulas is back bone the ms-excel.
To become a good excel operator, very firstly you’ll have to learn about basic commands and formulas of excel.

http://www.dreamstime.com/royalty-free-stock-photo-3d-learn-here-crossword-image21000275

BASICS OF EXCEL :-


What is a workbook
what is a sheet
what is a cell
what is cell address

Create a new blank workbook

Explore the Excel interface
Move within worksheet cells
Enter data
Select and format data
Copy and paste formatting and data
Edit data
Insert and resize columns
Save a workbook in a new folder
How to cut,copy & paste commands works
How to find the specific name/text

How to protect file or sheet: Encrypt an Excel file with a password so that it requires a password to open it.
How to hide or unhide columns/rows

 

Keyboard Shortcuts: Keyboard shortcuts allow you to do things with your keyboard instead of your mouse to increase your speed.
shortcut commands of excel like ( ctrl +c, ctrl+v, ctrl+x, ctrl+z, ctrl+y, ctrl+s etc)

Other shortcut keys in excel

The basic formulas of excel is here :-
1- How to insert or delete a column/row
2- How to add two numbers in excel
3- How to subtraction/ division/multiplication in excel

4- How to apply conditional formatting 

5- How to use filters

6- How to use VLOOKUP function 

7- How to use multiple IF statement

8-How to use AND

 

What is AND function

What is AND function

Ms Excel is an extremely powerful and useful data analytics software of Ms-office. AND functionality is manipulate  in multiple cells and all of the formulas tested value must be true before the function returns a TRUE value. If one of them condition will not be true, then the AND function returns a  FALSE  value.

The AND function allows the Excel user to evaluate or test multiple conditions. In the logical syntax of Excel , whether it be as part of an IF-THEN statement or a conditional formatting command, the AND function can become a powerful part of a logical statement.

The AND function needs at least two arguments or parameters to execute. Those arguments can be as simple or complex as you’d like or need them to be. They are separated in the AND function by a comma( , ) , just like all of the arguments in Excel functions are. It provides either a ‘true’ or ‘false’ result based on the arguments inside the AND function.

Syntax
=AND (Test1,Test2)

 Example :

=AND(C4>=100,D4>=100)

Result= TRUE , because both conditions are true. In the figure C4=200 & D4=600 , both value & arguments are greater then 100 so that the result is TRUE.

In the second case the result is FALSE because C5 is greater then 100 but D5 is not greater or equals to 100. One argument is false so AND function shows the FALSE. & the same logic in third case. 

 and

 

 

What Does It Do?

This function tests two or more conditions to see if they are all true.
It can be used to test that a series of numbers meet certain conditions.
It can be used to test that a number or a date falls between an upper and lower limit.
Normally the AND() function would be used in conjunction with a function such as =IF().

 

VLOOKUP FUNCTION SYNTAX IN EXCEL

What is VLOOKUP

VLOOKUP is a most useful formula in Excel. Unfortunately – it is also one of the most confusing for the beginner. In simple words, actually VLOOKUP function search a value in the range which mentioned in the formula.

The VLOOKUP function can sometimes be a better solution in a scenario. You can see a really good example of where you should learn how the VLOOKUP function works in one of the comments below.
That’s not to say that VLOOKUP is automatically a better solution:

According to Excel’s formula description, VLOOKUP  looks for a value in the workbook, worksheet, table, array or in a column of a table, and then returns a specific value in the same row from a column you specify.

HOW VLOOKUP WORKS

If you want to pick up a value from other sheet then VLOOKUP function is the most useful for that.

To dumb it down for you, VLOOKUP lets you pull information about your selected cells into your current sheet, from other sheets or workbooks where that value exists.

Example:

The Formula

The formula for VLOOKUP looks like this:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Actual figure of function will look like this.

=VLOOKUP(A2,MASTER,2,FALSE))

 

VLOOKUP WITH IF STATEMENT

=IF(A2=””,””,VLOOKUP(A2,MASTER,2,FALSE))

 

 vlokup

What this formula says :-

=IF(A2=””,””,VLOOKUP,A2,MASTER,2,FALSE))

It’s mean if A2 have any value then search that value in cloumn 2 of MASTER sheet, FALSE uses for exact value.

 

VLOOKUP WITH MATCH                                                                                

This table is used to find a value based on a specified name and month.                                                   

The =VLOOKUP() is used to scan down to find the name.                                                                             

The problem arises when we need to scan across to find the month column.                                                     

To solve the problem the =MATCH() function is used.                                                                                  

The =MATCH() looks through the list of names to find the month we require. It then calculates 

the position of the month in the list. Unfortunately, because the list of months is not as wide                   

as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is added to compensate.                                                                            

The =VLOOKUP() now uses this =MATCH() number to look across the columns and picks out the correct cell entry.                                                                                 

The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the row headings are not sorted.        

 

 

HOW TO USE IF FUNCTION IN EXCEL

IF FUNCTION 

IF FUNCTION/SYNTAX

This  tutorial explains how to use  IF function in excel with syntax and examples.

What is IF function ?

In Microsoft Excel IF function returns one value if the condition is TRUE, or another value if the condition is FALSE.

The IF function is a built-in function in Excel that is categorized as a Logical Function. It can be used as a worksheet function  in Excel. As a worksheet function, the IF function can be entered as part of a formula in a cell of a worksheet. A simple example and syntax is here.

Syntax

The syntax of IF function 

IF( condition, [value_if_true], [value_if_false] )

Example 1

if1

Based on the Excel spreadsheet above, the following IF examples would return:

=IF(A1>5, “Greater”, “Smaller”)

Result: “Greater”

 

Example 2 :

What Does It Do?
This function tests a condition.
If the condition is met it is considered to be TRUE.
If the condition is not met it is considered as FALSE.
Depending upon the result, one of two actions will be carried out.

Syntax
=IF(Condition,ActionIfTrue,ActionIfFalse)
The Condition is usually a test of two cells, such as A1=A2.
The ActionIfTrue and ActionIfFalse can be numbers, text or calculations.

Formatting
No special formatting is required.

Example 1
The following table shows the Sales figures and Targets for sales reps.
Each has their own target which they must reach.
The =IF() function is used to compare the Sales with the Target.
If the Sales are greater than or equal to the Target the result of Achieved is shown.
If the Sales do not reach the target the result of Not Achieved is shown.
Note that the text used in the =IF() function needs to be placed in double quotes “Achieved”.

=IF(C31>=D31,”Achieved”,”Not Achieved”)
=IF(C32>=D32,”Achieved”,”Not Achieved”)
=IF(C33>=D33,”Achieved”,”Not Achieved”)

if2

 

 

 

HOW  TO USE AND IN EXCEL

EXCEL CONDITIONAL FORMATTING

Excel conditional formatting


Excel conditional formatting is a really powerful feature when it comes to applying different formats to data that meets certain conditions. It can help you highlight the most important information in your spreadsheets and identify variances of cells’ values with a quick glance.

At the same time, Conditional Formatting is often deemed as one of the most intricate and obscure Excel functions, especially by beginners. If you feel intimidated by this feature too, please don’t! In fact, conditional formatting in Excel is very straightforward and easy to use, and you will make sure of this in just 5 minutes when you have finished reading this short tutorial.

The basics of conditional formatting

Cells, rows, or columns can be  formatted to change text, icon, font color or background color if they meet certain conditions. For example, if they contain a certain word, number or a value.

Open an excel spreadsheet.
Select the cells you want to apply format rules.
Click Format and then Conditional formatting. A toolbar will open to the right.
Create a rule.
Single color: Under “Format cells if,” choose the condition that you want to trigger the rule. Under “Formatting color, choose what the cell color or font color look like if condition is true.
Color scale: Under “Preview,” select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow Down Arrow.
Click Done.

The same as usual cell formats, you use conditional formatting in Excel to format your data in different ways by changing cells’ fill color, font color and border styles. The difference is that conditional formatting is more flexible, it allows you to format only the data that meets certain conditions.

You can apply excel conditional formatting to one or several cells, rows, columns or the entire table based on the cell contents or based on another cell’s value. You do this by creating rules (conditions) where you define when and how the selected cells should be formatted.

greaterthan-cndi

grtrtthan

STEPS .

  1. CLICK ON THE HOME TAB.

  2. CHOOSE CONDITIONAL FORMATTING

  3. HIGHLIGHT CELL RULES

  4. THAN CLICK ON GREATER THAN

grtrtthan

5. CLICK ON THE DROPDOWN LIST.

6. CLICK ON CUSTOM FORMAT.

7.CHOOSE A CUSTOM COLOR.

grtr3

Excel conditional formatting

What is a workbook

What is a workbook ?

Actually an Excel workbook is a file that contains one or more worksheets.
When you open ms-excel in your PC then a workbook generates itself, when a workbook a generated it has three worksheets by default. (sheet 1, sheet 2 and sheet 3) you can rename a worksheet according to you.You can also add or remove worksheets according to your need.

What is a worksheet ?

An excel worksheet is platform where you can enter your data and information or records, an excel worksheet is a spreadsheet which contains much more columns and rows , that you can use to organize various kinds of related information.

workbook-and-worksheet

 

How to learn Excel ?

HOW TO LEARN EXCEL

EXCEL LEARNING

How to learn excel .Excel is a most use full tool of ms-office.
These days, every job requires advance Excel skills. An organization wants these basic Excel skills –creating tables, format data, manipulate basic essential operations like(sum, subtraction, multiplication, division, average & summaries with filters and formulas, highlight data with conditional formatting, validation, charting , advance formulas and much more another thing which you will learn from here.

HOW TO BECOME AN EXCEL EXPERT
Excel is a huge application in itself with thousand of features and formulas.Formulas is back bone the ms-excel.
To become a good excel operator, very firstly you’ll have to learn about basic commands and  basic formulas of excel.


BASICS OF EXCEL :-
What is a workbook
what is a sheet
what is a cell
what is cell address
How to cut,copy & paste commands works
How to find the specific name/text
How to hide or unhide columns/rows
shortcut commands of excel like (crtl+c , ctrl+v , crtl+x , crtl+z , crtl+y , crtl+s etc)

smtemp

The basic formulas of excel is here :-
1- How to insert or delete a column/row
2- How to add two numbers in excel
3- How to subtraction/division/multiplication in excel


WHAT IS CELL & CELL ADDRESS

A cell is an intersection between a row and a column in a spreadsheet.Each cell in a spreadsheet can contain any value that can be called using a relative cell reference or called upon using a formula.


A “cell reference” means the cell to which another cell refers. For example, if in cell A1 you have =B1. Then A1 refers to B1. 


 

HOW TO CUT, COPY & PASTE IN EXCEL

These are  commands which must use in excel. Cut,copy,paste command is so simple.

Control+c = copy

Control+x = cut

Control+v= paste

Other useful commands in excel


 

What is a Workbook ?

What is a Worksheet ?