Finding frequency means counting how many times a value shows up in your data

Excel has a built-in function called COUNTIF that counts how many cells match what you're looking for. If you have a list of 200 sales transactions and want to know how many times "Widget A" appears, COUNTIF gives you that number in seconds. The function works the same way whether your data is in one column, spread across rows, or mixed with other information.

The simplest frequency task — counting one specific value — takes one line of formula. More complex tasks, like creating a frequency table that shows every unique value and how often each one appears, require a few extra steps but still use the same basic tools.

Key Takeaways

  • COUNTIF is the main function for frequency: type =COUNTIF(range, criteria) where range is your data and criteria is what you're counting.
  • To count how many times "yes" appears in cells A1 through A100, write =COUNTIF(A1:A100,"yes") and press Enter.
  • You can count text, numbers, or cells that meet a condition like "greater than 50" by changing what goes in the criteria spot.
  • A frequency table showing all unique values and their counts requires listing unique values first, then using COUNTIF next to each one.

The COUNTIF formula for counting one value

Open your spreadsheet and click on an empty cell where you want the count to appear. Type the formula exactly as shown: =COUNTIF(A1:A100,"Widget A"). Replace A1:A100 with the actual range of cells holding your data, and replace "Widget A" with whatever you're counting. Press Enter, and Excel shows the number of matches.

The range tells Excel where to look. If your data is in column B from row 2 to row 500, write B2:B500. If it's in a single row across columns C through M, write C1:M1. Excel counts every cell in that range that exactly matches your criteria.

For text values, put them in quotes: =COUNTIF(D1:D50,"approved"). For numbers, you can skip the quotes: =COUNTIF(E1:E100,42). Both work the same way — Excel counts and returns a number.

Counting values that meet a condition instead of exact matches

COUNTIF also counts cells that meet a rule rather than match an exact value. If you want to count how many sales were over $500, use =COUNTIF(F1:F200,">500"). The greater-than sign goes in quotes along with the number. For less than, use "<500". For not equal to, use "<>500".

This works with dates too. To count how many entries are from 2024 or later, write =COUNTIF(G1:G300,">=2024"). Excel treats the date as a number internally, so the comparison operators work the same way.

You can also use wildcards for partial text matches. If you want to count all cells containing the word "error" anywhere in the text, write =COUNTIF(H1:H100,"*error*"). The asterisks mean "any characters before and after". To count cells that start with "John", use =COUNTIF(I1:I100,"John*").

Building a frequency table to see all values and their counts

A frequency table shows every unique value in your data and how many times each appears. Start by listing all the unique values in one column. If your original data is in column A, put the unique values in column C. You can type them manually if there are only a few, or copy them from column A and remove duplicates.

To remove duplicates in Excel, select your data, go to the Data tab, and click Remove Duplicates. Excel removes the duplicate rows and leaves you with one of each value. Copy those unique values to column C.

Now in column D, next to each unique value, create a COUNTIF formula. In cell D1, write =COUNTIF($A$1:$A$100,C1). The dollar signs lock the range so it doesn't change when you copy the formula down. Press Enter, then copy this formula down to match every unique value in column C. Each row now shows a value and how many times it appears in your original data.

Using COUNTIFS when you need to count based on multiple conditions

COUNTIFS (with an S) counts cells that meet two or more conditions at the same time. If you have sales data with columns for product name, region, and amount, and you want to count how many times "Widget A" was sold in the "North" region, use =COUNTIFS(A1:A100,"Widget A",B1:B100,"North").

The pattern is: range one, criteria one, range two, criteria two, and so on. You can add as many condition pairs as you need. To count sales over $500 in the North region, write =COUNTIFS(B1:B100,"North",C1:C100,">500").

Common mistakes and how to fix them

The most common error is mismatched range sizes. If you write =COUNTIFS(A1:A100,"Widget A",B1:B50,"North"), the ranges are different lengths and Excel returns an error. Both ranges must have the same number of rows. Check that A1:A100 and B1:B100 both start at row 1 and end at row 100.

Another mistake is forgetting quotes around text criteria. =COUNTIF(A1:A100,Widget A) fails because Excel thinks Widget and A are cell references, not the text you want to count. Always use quotes: =COUNTIF(A1:A100,"Widget A").

If your count returns zero when you expect a higher number, the criteria might not match exactly. Check for extra spaces, different capitalization, or different spelling. COUNTIF is case-insensitive (it treats "Yes" and "yes" the same), but it is space-sensitive. A cell containing " yes" with a leading space will not match "yes".

When to use frequency instead of sorting or filtering

Sorting and filtering let you see your data organized, but they don't give you a count. Frequency formulas give you the count without changing how your data looks. If you need to report how many customers are in each region or how many transactions fall into each price bracket, a frequency table is faster than manually counting sorted groups.

Frequency also updates automatically. If you add new data to your original list, the COUNTIF formulas recalculate when ready. A manual count would need to be redone every time.

Frequently Asked Questions

Can I count cells that are blank?

Yes. Use =COUNTIF(A1:A100,"") to count empty cells, or =COUNTIF(A1:A100,"<>") to count cells that are not empty. The empty quotes tell Excel to look for cells with nothing in them.

What if my data has different capitalization, like "Yes", "yes", and "YES"?

COUNTIF treats all of these as the same and counts them together. If you need to count only cells with exact capitalization, you would need a different function like SUMPRODUCT with EXACT, but for most frequency work, the case-insensitive behavior of COUNTIF is what you want.

How do I count how many cells contain a specific word but not the whole cell?

Use the wildcard pattern =COUNTIF(A1:A100,"*word*"). The asterisks match any characters before and after. This counts cells containing "word" anywhere in the text, whether it's "keyword", "word count", or just "word".

Can I count based on the color of a cell or whether it's bold?

COUNTIF cannot count by formatting. It only counts based on the value inside the cell or a condition applied to that value. If you need to count colored cells, you would need a different approach, such as manually tagging cells with a helper column that marks the color.

What's the difference between COUNTIF and COUNTIFS?

COUNTIF counts cells matching one condition. COUNTIFS counts cells matching multiple conditions at the same time. If you only need one condition, COUNTIF is simpler. If you need two or more conditions, use COUNTIFS.