Counting Based on a Specific Criterion
Do you know there is a really fast and easy way to count rows that meet specific criteria? For instance, if you have over a hundred rows of spending data, like the hypothetical data below, how can you quickly find out how many times you bought something in the month of October?
The answer is, with a function called “COUNTIF.”
COUNTIF is a function that allows you to count something based on a single criterion. It “counts” your data, “if” some sort of criteria is met. Using the example data set, let’s count how many times you bought something in October, November, and December. Download the data set to follow along.
Let’s do our calculations off to the side, in columns H & I. You’ll enter your COUNTIF formula in cell I2.
In the formula bar, type in “=COUNTIF(B2:B142,”OCTOBER”).” This is telling Excel to look in the range “B2:B142” (which is where our months are), and count all the instances of “OCTOBER.”
Alternatively, instead of typing out your criteria, you could refer to a cell. For instance, you could have also typed “=COUNTIF(B2:B142,H2).”
Notice that when you typed out OCTOBER, there were quotes around it, whereas when you referred to H2, there were no quotes. This is because OCTOBER is text and H2 is a cell reference. Whenever you use text inside a formula, you need to put quotes around it.
I like to use cell references over typing in actual text for my criteria. First of all, it’s faster to click a cell rather then typing out a whole word. And second, more importantly, cell references allow you to easily autofill your formula. Just remember to use absolute rather than relative cell references for the data range if you are autofilling or dragging your formulas down.
This is what your COUNTIF section should look like after you filled in the formula from I2 to I3 and I4.
So now you know how to count data based on one criterion, but what if you wanted to count data based on multiple criteria? Stay tuned for my next blog post on COUNTIFS