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.

HOW TO USE MULTIPLE IF IN EXCEL

HOW TO USE MULTIPLE IF IN EXCEL

Using multiple IF statements in Excel

This tutorial will tell you, How to use multiple IF in excel.

The IF() function in Excel allows you to evaluate a logical condition which has two possible results  and calculate a different value for each result. However, sometimes you need to work with conditions where there are more than two possible results. That’s where multiple, or nested, IF functions come in handy. In this tutorial we’ll cover how to use nested IF functions to manipulate a logical condition.

This tutorial assumes you are already familiar with the IF function. If you aren’t, you can learn how to use IF statements here.

Remember that the IF function works by evaluating a logical test – a calculation which can only be TRUE or FALSE.  It  includes a calculation to perform if the logical test is TRUE, and another calculation if it is FALSE.

Nested IF functions work by replacing one or both of the TRUE/FALSE calculations with another IF function.

This formula can be confusing  for first time. Let’s have a look on the syntax & example of multiple/nested IF.

=IF(B2=”APPLE”, “FRUIT”, IF(B2=”TOMATO”, “VEGETABLE”, IF(B2=”SALT”, “SPICES”, “”)))

nested-if

Note :-

If you’ll type TOMATO in B2 then the C2 will change to VEGETABLE , Or if you type SALT in B2 then C2 value will be change to SPICES.

 

Another example of nested IF which calculates the grade of different persons according to score.

nestif

=IF(C4<64,”F”,IF(C4<73,”D”,IF(C4<85,”C”,IF(C4<95,”B”,”A”))))

Here is three nest IF IN this formula which calculate the grade.

So now if you change the score in column C then the grades will be change accordingly.

HOW TO USE VLOOKUP 

HOW TO AND FUNCTION 

 

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

 

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

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 ?