How to count sets of cells located in two columns in Excel
For this example, we will attempt to count sets of cells located in two columns in Excel. What does this mean? In our example we have two columns with different values and we’d like to find out how often two sets of values appear adjacent to each other. You can look at the image and see that a and 4 appear together 2 times, but of course, what if you are working with lots of values? Here is how to check this using a formula. We will be using the SUMPRODUCT Function.
- Layout your data as shown in the picture or use your own layout.
- Select Cell C5 and enter the following formula: =SUMPRODUCT(–(A1:A5=”a”),–(B1:B5=4))
- You can modify this by changing the values a and 4 as well as changing the range of values it checks.
You should now be set!
- Use the COUNT function to count cells containing numeric data
- How to Use the COUNTA function to count cells containing text
- How to Use the COUNTBLANK function to count empty cells
- How to Use the COUNTA function to count cells containing data
- Use the ISODD and COLUMN functions to determine odd columns
Nicole
You give the formula using “-” before the ranges, yet I notice in the fx field it’s shown as “–”. I’ve seen other examples on the net using “–”. Which is it?
August 19th, 2008 at 1:33 pmNicole
Sorry it didn’t reproduce as typed, I mean the difference between what may be and “em” dash and a pair of shorter hypens. Thanks.
August 19th, 2008 at 1:34 pmKatie Piatt
Fantastic - excellent tip, saved me loads of time.
October 24th, 2008 at 6:28 am