# 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:

1. Select the cells that contain the criteria you want to count.
2. Type the following formula in a blank cell: =SUM(COUNTIF(A1:A5,A1:A5)).
3. Press Ctrl + Shift + Enter to turn the formula into an array formula.
4. 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

Taking too long?

| Open in new tab

# Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

## Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

# Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

`Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.`

# Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!