Excel can perform a variety of really nice data analysis features for you. We’ve already touched upon how you can filter data. But you can also look for other connections, or screen large numbers of cells to determine how often something occurs.

COUNTIF

COUNTIF is a way for you to ask Excel to count how many times a certain piece of information appears in your worksheet. For example, perhaps you want to know how often “shirt” appear in an inventory list. All you need to do is ask Excel to count the number of cells that contain the word “shirt.”

1. Determine which cells you want Excel to look at. In our example, we will look at A2 though A13.
2. Click on the cell you wish your count to be displayed in.
3. Type the formula for a count
   =COUNTIF(A2:A13, “shirt”)

Here you are telling Excel which cells to examine—A2 through A13—and what to look for: “shirt.” Note that your text must match exactly what is typed in the cells, and if you are looking for a specific word it needs to be enclosed in quotation marks (so “shirt” instead of shirt).

4. Hit enter and your results will appear.

IF

Another commonly used function in Excel is the “IF” function. In this case, you are asking Excel to look for something and then tell you if that something occurred. For example, perhaps you want to compare whether your monthly expenses were under your monthly budget. That is the scenario we will look at in our example.

In this case, let us just ask for a simple “yes” or “no” answer. Looking at the screenshot below, you can see how the worksheet has all the data at hand. We are looking for whether the information in the C column is less than the information in the B column. We would like the D column to display the answer (yes or no).

1. Click on D2 and enter the “IF” function for what you want Excel to compare and do.
   =IF(C2<B2, “Yes,””No”)

2. You do not have to manually reenter the formula into the other cells in D. Instead you can copy and paste the formula from D2 into D3, D4, and so on. Each time you do this, the formula should automatically update with the correct cell number to compare.

As you can see, the D cells begin to display “Yes” or “No.” “Yes” means that the expenses in the C column were less than the monthly budget entered into the B column. “No” means that expenses were higher than the budget. Just as in the COUNTIF function, you need to enclose text in quotation marks.

Contribute!

Improve this pageLearn More