Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist
Excel is a powerful tool for data analysis and it provides a variety of functions that can help make your life easier as a data analyst. One such function is COUNTIF, which allows you to count the number of cells in a range that meet a certain criteria. But what if the range is not contiguous? In other words, what if the cells you want to count are scattered throughout the sheet and not in a single block?
Fortunately, you can still use the COUNTIF function to count non-contiguous ranges. The key to making this work is to use the array formula feature in Excel. An array formula is a type of formula that performs multiple calculations on one or more sets of values and then returns a result in a single cell.
To count non-contiguous ranges with the COUNTIF function, you’ll need to first select the cells that contain the criteria you want to count. Then, you’ll need to use the COUNTIF function along with the array formula feature to perform the calculation. Here’s how it’s done:
- Select the cells that contain the criteria you want to count.
- Type the following formula in a blank cell: =SUM(COUNTIF(A1:A5,A1:A5)).
- Press Ctrl + Shift + Enter to turn the formula into an array formula.
- The result will be displayed in the cell you entered the formula in.
Note that the above example uses the SUM function to add up the results of the COUNTIF function, which is required when using an array formula. You can also use other functions like AVERAGE, MIN, or MAX if needed.
It’s important to remember that when using an array formula, you need to press Ctrl + Shift + Enter to turn the formula into an array formula. If you forget to do this, the formula will not work as expected and you’ll get an error message.
In conclusion, counting non-contiguous ranges in Excel can be done easily with the COUNTIF function and the array formula feature. This technique can be useful when you need to count cells that meet a certain criteria, but the cells are scattered throughout the sheet. With this knowledge in hand, you’ll be able to tackle a wide range of data analysis tasks with confidence!
Excel Example for Data Analyst – COUNTIF with non-contiguous range
Loading...
Reload document 
Open in new tab Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.