COUNTIF: Excel Formula to Count Cells between Two Numbers

count cells between two numbers
جدول المحتويات

Count Cells between Two Numbers using Excel COUNTIF Formula

ماذا يكون COUNTIF Function:

If you are familiar with Microsoft اكسل you would know that COUNTIF is the function use to count the number of cells that contains value.

In this post we are going to show you a formula to count the number of cell that contains value between two numbers in a particular column or a range of cells by using the combination of two COUNIF functions in the same formula.

لماذا this formula it important:

This formula is very helpful when you don’t want the count of entire cells in a column or a particular range of cells but only the count cells between two numbers.

For eg: You have a list of 10 students with their marks for a particular subject and you want to know out of 10 students, the count of students who scored 50 to 80.

Countif FormulaPin

كيف to Count Cells between Two Numbers using COUNTIF formula:





I’ll explain you the formula with the same example above, its pretty simple; just a small subtract logic we are applying with COUNTIF function.

  • First it counts all the numbers above 50. In this example we have 85, 75, 60, 55, 46, 62, 67, 28, 45, 30 that means 6 numbers are above 50. That’s what the first section of the formula does.

=COUNTIF(B2:B11,”>=50″)

  • Secondly we are counting all the numbers above 80 and subtracting it from the first count.  In this example we have only 1 number above 80 ie 85.

-COUNTIF(B2:B11,”>80″)

In short 6-1=5, and that’s what the result we are looking for, count of students who scored between 50 to 80.

So the complete formula will be like the below:

=COUNTIF(B2:B11,”>=50″)-COUNTIF(B2:B11,”>80″)

Instead of mentioning a specific range of cells if you want to count from entire column then your formula should be something like the below:

=COUNTIF(B:B,”>=50″)-COUNTIF(B:B,”>80″)

If you want to pick the starting and ending numbers/the between numbers from different cells instead of mentioning it directly in the formula (In this example 50 and 80), you can mention that cell value in the formula like below:

=COUNTIF(B2:B11,”>” &E1)-COUNTIF(B2:B11,”>”&F1)

So now you know how to count cells between two numbers in Excel using simple COUNTIF function.


أنسون أنتوني
أنسون أنتوني
أنسون هو مؤلف مساهم ومؤسس في www.askeygeek.com. لقد كان تعلم أي شيء جديد هو شغفه دائمًا، ASKEYGEEK.com هو نتيجة شغفه بالتكنولوجيا والأعمال. لقد حصل على عقد من الخبرة المتنوعة في الاستعانة بمصادر خارجية للعمليات التجارية، والمالية والمحاسبة، وتكنولوجيا المعلومات، والتميز التشغيلي وذكاء الأعمال. خلال فترة عمله، عمل في مؤسسات مثل Genpact وHewlett Packard وM*Modal وCapgemini في أدوار ومسؤوليات مختلفة. بعيدًا عن الأعمال والتكنولوجيا، فهو من عشاق الأفلام ويقضي ساعات معًا في مشاهدة وتعلم السينما وصانع أفلام أيضًا!

اترك تعليقاً

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *

تهانينا!
لقد فعلتها،
لا تغلق!

الحصول على ما يصل الى 60.000 أرصدة شخصيات UberTTS مجانًا!!!

هذه النافذة المنبثقة لن تظهر متروك لكم مرة أخرى!!!

أوبر تي تي إس
Share to...