Скачать или смотреть Excel - Counting Between a Range in Excel: Using COUNTIFS Function - Episode 1508

Microsoft Excel Tutorial: Counting Between a Range in Excel: Using COUNTIFS Function.

Welcome to the MrExcel podcast. In this episode, we will be discussing how to count between a range in Excel. This is a common question that many Excel users have, and I'm here to provide you with a solution. So, let's dive in!

The question for today's episode was sent in by one of our viewers. They had a series of numbers in column A and were able to use the COUNTIF function to count all values less than 10 and less than 20. However, they were struggling to combine these two criteria and count everything between 10 and 20. Well, I have good news for you. If you have Excel 2007 or newer, there's a function called COUNTIFS that can help you with this task.

COUNTIFS is like the plural version of COUNTIF, and it allows you to check for multiple conditions. In this case, our criteria range will be the same (A2:A200), and we want to count values that are greater than or equal to 10 and less than 20. The formula for this would be =COUNTIFS($A$2:$A$200,"=10",$A$2:$A$200,"20"). However, this function is not available in Excel 2003 or older versions.

If you're using Excel 2003 or earlier, you can still achieve the same result by using other functions such as SUMPRODUCT or DCOUNT. However, if you have the option to save your file as .xlsm, then you can use the COUNTIFS function. If not, you can use a combination of COUNTIF and subtracting the two criteria to get the desired result. I know it may feel like cheating, but sometimes we have to work with what we have.

So, there you have it. COUNTIFS is definitely the better option if you have Excel 2007 or newer. But if you're using an older version, you can still get the job done with a little bit of creativity. I hope this episode was helpful for you. Thank you for tuning in to the MrExcel netcast. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Table of Contents:
(00:00) Count Between A Range
(00:15) Using COUNTIF to count values 10 and 20
(00:28) Difficulty combining both counts
(00:38) Solution: COUNTIFS function
(00:48) Explanation of COUNTIFS
(01:08) Criteria range and conditions
(01:26) Example formula
(01:42) Alternative solutions for older versions
(02:16) Clicking Like really helps the algorithm


The Question: "I have a =COUNTIF to count values less than 10 and another to count values less than 20; but how do I count the values that fall between 10 and 20?"
Ever since Excel 2007, the COUNTIFS function allows for multiple criteria. Follow along with Bill in Episode #1508 to learn how to solve this question - even if you are using Excel 2003 or earlier!

This video answers these common search terms:
how to count between excel
how to count between two numbers in excel
how to use countif between two values in excel


The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...