تابع COUNTIFS در اکسل – شمارش سلول ها با چند شرط به زبان ساده + مثال

تابع COUNTIFS برای شمارش تعداد سلول‌های مشخص از جدول اکسل با یک یا چند شرط کاربرد دارد. بر خلاف تابع شمارشی COUNTIF که فقط یک شرط را می‌توانیم در فرمول آن بنویسیم، در این تابع چند شرط قابل تعریف است. بنابراین اگر همه شرط‌ها برقرار باشند، تابع COUNTIFS تعداد سلول‌ها را به‌عنوان نتیجه نهایی نشان می‌دهد. در این مطلب از مجله فرادرس فرمول‌نویسی ساده و حرفه‌ای با تابع COUNTIFS در اکسل را همراه مثال یاد می‌گیریم. سپس با کاربردهای مختلف آن در کسب و کارها نیز آشنا می‌شویم.

فرمول کلی تابع COUNTIFS در اکسل

فرمول کلی تابع COUNTIFS به شرح زیر است.

=COUNTIFS(range1, "criteria1", [range(n), "criteria(n)"]…)

که در آن هر یک از آرگومان‌ها به شکل زیر تعریف می‌شوند.

• range1 (اجباری): اولین محدوده از سلول‌ها که می‌خواهیم تعداد آن‌ها را در صورت برقراری شرط اول بشماریم.
• criteria1 (اجباری): اولین شرط که برای شمارش در اولین محدوده مشخص شده جدول تعریف می‌کنیم.
• range(n) (اختیاری):‌ سایر محدوده سلول‌ها که می‌خواهیم تعداد آن‌ها را در صورت برقراری شرط‌های متناظر بشماریم.
• criteria(n) (اختیاری): سایر شرط‌هایی که برای شمارش در محدوده‌های متناظر جدول تعریف می‌کنیم.

البته در فرمول تابع COUNTIFS حداکثر ۱۲۷ شرط قابل تعریف است و اعداد تا ۱۵ رقم را می‌شمارد. با این حال در هر شرط فرمت‌های مختلفی مانند عدد، متن، تاریخ، علامت‌های مقایسه‌ای (کوچک‌تر، بزرگ‌تر، مساوی، نامساوی) یا وایلد کارت را می‌كتوانیم تعریف کنیم.

فرمول کلی تابع COUNTIFS زیاد پیچیده نیست و با تعریف درست شرط و انتخاب محدوده، به‌راحتی محاسبه انجام می‌شود. اما در صورت ترکیب با سایر توابع اکسل، فرمول‌نویسی‌ آن حرفه‌ای‌تر می‌شود. یادگیری این روش‌ها نیاز به افزایش مهارت فرمول‌نویسی اکسل دارد که در ادامه بحث مثال‌هایی را بررسی می‌کنیم. با این حال به عنوان یادگیری تکمیلی، پیشنهاد می‌کنیم فیلم آموزش توابع و فرمول‌نویسی در اکسل در فرادرس را نیز مشاهده کنید.

نکات مهم در فرمول‌نویسی با تابع COUNTIFS

به‌جز رعایت تعریف درست محدوده سلول و شرط مطابق فرمول، برای جلوگیری از ایجادی خطای احتمالی رعایت نکات زیر بسیار اهمیت دارند.

فیلم آموزش ترفندهای کاربردی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

۱. استفاده از علامت«" "» همراه متن، عدد و علامت‌های مقایسه‌ای

عبارت‌های متنی فرمول تابع COUNTIFS را همیشه داخل علامت "" می‌نویسیم. این کار برای اعداد ضروری نیست. اما اگر یکی از علامت‌های مقایسه‌ای به تنهایی یا همراه عدد داشته باشیم، لازم است آن‌ها را داخل "" بنویسیم.

موارد زیر نمونه‌هایی فرضی از فرمول‌نویسی در هر یک از حالت‌های بالا هستند.

مثال فرمول‌نویسی با متن

=COUNTIFS(range,"فرادرس")

مثال فرمول‌نویسی با عدد

=COUNTIFS(range,100)

مثال فرمول‌نویسی با عدد و علامت مقایسه‌ای

=COUNTIFS(range,">50")

۲. استفاده از علامت‌ «&» در محاسبات ترکیبی

زمانی‌که داخل شرط تابع COUNTIFS از یک تابع دیگر یا یک آدرس سلول همراه علامت‌های مقایسه‌ای استفاده می‌کنیم، علامت & را به‌عنوان اتصال‌دهنده دو بخش می‌نویسیم. نمونه‌های زیر مواردی از این فرمول‌نویسی ترکیبی هستند.

=COUNTIFS(B2:B8,">="&DATE(2025,1,1))

=COUNTIFS(A2:A8,">"&E1)

البته در حالت دوم اگر شرط فقط به آدرس یک سلول اشاره کند و از علامت‌های مقایسه‌ای استفاده نکنیم، نیازی به این علامت نداریم. در این مورد مانند مثال زیر فرمول را به‌صورت معمولی می‌نویسیم.

=COUNTIFS(A2:A8,E1)

۳. یکسان بودن اندازه محدوده‌ سلول

هنگام فرمول‌نویسی لازم است تمام محدوده تعریف شده در شرط‌ها یکسان باشند. به‌عنوان مثال اگر criteria_range1 دارای پنج ردیف و محدوده دوم criteria_range2 هشت ردیف داشته باشد، با پیام خطای #VALUE! روبرو می‌شویم.

۴. توجه به برقرار بودن همه شرط‌ها

تابع COUNTIFS در حالت پیش‌فرض مطابق عملگر AND محاسبه را انجام می‌دهد. به این معنا که سلول‌ها را زمانی می‌شمارد که همه شرط‌های تعریف شده در فرمول، برقرار باشند. بنابراین اگر می‌خواهیم نتیجه نهایی در صورت درست بودن یک شرط هم نمایش داده شود، استفاده از یک تابع COUNTIFS جوابگو نیست.

۵. حساس نبودن به حروف کوچک و بزرگ انگلیسی

تابع COUNTIFS حساس به بزرگ یا کوچک بودن حروف انگلیسی نیست. بنابراین اگر بخواهیم عبارت متنی با حروف بزرگ یا کوچک انگلیسی را بشماریم، از تابع EXACT همراه تابع COUNTIFS استفاده می‌کنیم.

مثال فرمول‌نویسی ساده با تابع COUNTIFS در اکسل

فرمول‌نویسی ساده با تابع COUNTIFS حالت‌های معمولی کاربرد آن با کمترین تغییر در ساختار اصلی آن است. مهم ترین این موارد که همراه مثال آن‌ها را توضیح می دهیم، به شرح زیر هستند.

1. شمارش سلول‌ها با دو شرط
2. شمارش سلول‌های خالی

فیلم آموزش شمارش سلول ها در اکسل + تابع COUNTIF و COUNTIFS (رایگان) در فرادرس

کلیک کنید

۱. شمارش سلول‌ها با دو شرط

ویژگی تابع COUNTIFS امکان تعریف چند شرط برای شمارش سلول‌ها است. نوشتن دو شرط، ساده‌ترین حالت فرمول‌نویسی با این تابع برای تعداد داده‌های کم محسوب می‌شود.

مثال

در جدول فرضی زیر حداکثر میزان درآمد کارشناسان ارشد یک شرکت در سال‌های مختلف را داریم. حال می‌خواهیم تعداد افرادی که حقوق ماهیانه بالاتر یا مساوی ۱۰۰ میلیون تومان در سال ۱۴۰۴ را داشته‌اند، حساب کنیم.

مطابق جدول، دو محدوده شمارش C2:C6 برای سال و B2:B6 برای حقوق دریافتی داریم. همچنین در فرمول تابع COUNTIFS لازم است دو شرط حقوق بالاتر یا مساوی از ۱۰۰ میلیون تومان و سال دریافت را تعریف کنیم.

بنابراین با نوشتن فرمول =COUNTIFS(C2:C6,1404,B2:B6">=100") تعداد کارشناسانی که این دو شرط به‌صورت هم زمان در مورد آن‌ها صادق است را می‌شماریم.

۲. شمارش سلول‌های خالی

برای فرمول‌نویسی سلول‌های خالی با تابع COUNTIFS از علامت "" و سلول‌های غیرخالی از علامت "<>" استفاده می‌کنیم.

مثال

در جدول فرضی زیر وضعیت پرداخت سفارش‌های مختلف را داریم. حال اگر بخواهیم فاکتورهای پرداخت شده در جدول که با علامت * مشخص هستند را بشماریم، فرمول =COUNTIFS(C2:C6,"<>") را می‌نویسیم.

البته علامت "<>" فرم کلی برای شمارش سلول‌هایی است که شامل عدد، متن یا هر عبارت دیگری هستند. در این مثال از آنجا که سلول‌های غیرخالی فقط با علامت * نمایش داده شده‌اند، فرمول =COUNTIFS(C2:C6,"*") نیز قابل استفاده است. برای شمارش سلول‌های خالی نیز فرمول =COUNTIFS(C2:C6,"") را می‌نویسیم.

مسیر میانبر فرمول‌نویسی با توابع اکسل همراه فرادرس

یادگیری، شرط اول انجام محاسبات حرفه‌ای با توابع اکسل است. با توجه به محبوب بودن این نرم‌افزار مراجع مختلفی برای آموزش فرمول‌نویسی وجود دارد. با این حال مجموعه فیلم‌های آموزشی تهیه شده در فرادرس به‌دلیل جامع بودن، یکپارچگی مطالب، تدریس توسط اساتید شناخته شده و امکان دریافت گواهینامه معتبر فرادرس راهی میانبر برای برطرف کردن نیازهای کاربران است.

پیشنهاد اول در مسیر یادگیری مقدماتی تا حرفه‌ای فرمول‌نویسی با توابع اکسل، مشاده فیلم‌های منتخب آموزشی زیر است.

• فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش تحلیل داده‌های کسب و کار با اکسل همراه گواهینامه در فرادرس

همچنین در دو مجموعه فیلم آموزش زیر نیز کاربران می‌توانند با توجه به نیاز خود مورد دلخواه را انتخاب کنند.

• مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس
• مجموعه فیلم آموزش اکسل برای کسب و کارها در فرادرس

مثال فرمول‌نویسی حرفه‌ای با تابع COUNTIFS

سطح پیشرفته کاربرد تابع COUNTIFS در اکسل استفاده از آن در ترکیب با سایر توابع است. به این شکل می‌توانیم محاسبات متنوعی را انجام دهیم. البته تنوع این توابع بسیار است و در این بخش چند نمونه پرکاربرد زیر را با تابع COUNTIFS توضیح می‌دهیم.

1. ترکیب با وایلدکارت‌ها
2. شمارش سلول‌های تاریخ‌دار
3. اجرای منطق OR
4. شمارش سلول‌های دارای عبارت تکراری

در ادامه بحث نحوه فرمول‌نویسی با هر یک را همراه مثال یاد می‌گیریم.

۱. ترکیب با وایلدکارت‌ها

تابع COUNTIFS در اکسل از سه «وایلد کارت» (Wildcard) زیر پشتیبانی می‌کند.

• ستاره (*): تطابق دقیق با چند کاراکتر.
• علامت سوال (؟): تطابق دقیق با یک کاراکتر.
• تیلدا(‍‍‍‍‍‍‍‍‍‍‍~): شمارش واقعی وایلدکارت‌ها.

وایلدکارت‌ها در اکسل کاربرد بسیاری در جستجوی داده‌ها نیز دارند که در مطلب زیر نکات تکمیلی را همراه مثال توضیح داده‌ایم.

مثال

جدول فرضی زیر میزان فروش شعبه‌‌های یک شرکت در شهرهای مختلف را نشان می‌دهد.

حال اگر بخواهیم انواع مختلف وایلدکارت‌ها را در فرمول‌نویسی استفاده کنیم، مدل‌های شمارش متنوعی داریم که انعطاف‌پذیری محاسبه را بیشتر می‌کند. در تصویر زیر چند نمونه را توضیح داده‌ایم.

۲. شمارش سلول‌های تاریخ‌دار

تاریخ در اکسل، یک عدد سریالی محسوب می‌شود. بنابراین برای شمارش سلول‌های تاریخ‌دار می‌توانیم به‌راحتی از عملگرهای کوچک‌تر، بزرگ‌تر یا مساوی استفاده کنیم. البته به شرطی که فرمت سلول را از بخش تنظیمات روی «Dates» قرار دهیم. اما برای شمارش سلول‌های تاریخ‌دار با تابع COUNTIFS دو حالت مختلف پیش می‌آید که در مثال‌های زیر آن‌ها را توضیح می‌دهیم.

مثال

در حالت اول اگر تاریخ ثابت و مشخص است، آن را در یک سلول جداگانه مانند A10 می‌نویسیم. سپس در فرمول با ارجاع دادن به آن سلول، با علامت‌های مقایسه‌ای، تاریخ‌های برابر، قبل یا بعد از آن تاریخ مشخص را می‌شماریم.

اما اگر تاریخ‌ها متغیر هستند، از تابع DATE در ترکیب با تابع COUNTIFS استفاده می‌کنیم. در این حالت برای مثال فرضی قبل، از فرمول‌های زیر برای شمارش تاریخ‌ها استفاده می‌کنیم.

فرمول شمارش سلول‌های قبل از ماه مارچ:

=COUNTIFS(A2:A8,"<"&DATE(2025,3,1))

فرمول شمارش سلول‌ها بعد از ماه مارچ:

=COUNTIFS(A2:A8,">"&DATE(2025,3,1))

فرمول شمارش سلول‌ها در ماه مِی ۲۰۲۵:

=COUNTIFS(A2:A8,">="&DATE(2025,5,1))

با توجه به این فرمول‌ها نتیجه شمارش به شکل تصویر زیر خواهد بود.

برای فرمول‌نویسی با تاریخ‌های فارسی لازم است نکات خاصی را در تبدیل تاریخ‌ها رعایت کنیم که در مطلب زیر از مجله فرادرس آن‌ها را توضیح داده‌ایم.

۳. اجرای منطق OR در فرمول

در حالت معمولی تابع COUNTIFS چند شرطی، نتیجه محاسبه را فقط در صورت برقرار بودن همه شرط‌ها نشان می‌دهد. اما اگر بخواهیم فرمولی با این تابع بنویسیم که با برقراری حداقل یک شرط، نتیجه مورد نظر نمایش داده شود، از ترکیب چند تابع COUNTIFS استفاده می‌کنیم. به این شکل منطق تابع OR را در فرمول اجرا می‌کنیم.

مثال

در جدول فرضی زیر میزان سود و درآمد سالانه چند شرکت آورده شده است.

حال می‌خواهیم تعداد شرکت‌هایی که میزان درآمد آن‌ها بالاتر از ۵۰۰۰ میلیون دلار یا سود سالانه کمتر از ۲۰۰۰ میلیون دلار است را بشماریم. برای این کار فرمول زیر را می‌نویسیم.

=COUNTIFS(B2:B7,">5000")+COUNTIFS(C2:C7,"<2000")

بنابراین همان‌طور که مشخص است، فرمول با اجرای منطق OR محاسبه را انجام می‌دهد..

۴. شمارش سلول‌های دارای عبارت تکراری

یکی دیگر از کاربردهای پر استفاده تابع COUNTIFS در اکسل مشخص کردن تکراری بودن یک عدد یا متن مشخص در جدول است. در این حالت اگر تابع، تکرار عبارت را تشخیص دهد، با عبارت «TRUE» این موضوع را نمایش می‌دهد.

مثال

در جدول فرضی زیر نام تعدادی دانشجو و رشته تحصیلی آن‌ها آورده شده است.

برای مشخص کردن ردیف‌هایی از جدول که نام دانشجو و رشته تحصیلی در آن‌ها تکراری است، فرمول زیر را برای ردیف اول می‌نویسیم.

=COUNTIFS($A$2:$A$7, A2, $B$2:$B$7, B2) > 1

این فرمول فقط برای ردیف اول است و برای اجرا در بقیه سلول‌ها لازم است، آن را کپی کنیم. بنابراین برای تکرار فرمول متناسب با هر ردیف، آدرس همه سلول‌ها را داخل علامت $ می‌گذاریم.

نتیجه نهایی بعد از کپی کردن فرمول به شکل تصویر زیر است..

یک حالت دیگر در این مثال وقتی است که بخواهیم فقط تعداد تکرار ترکیب «نام دانشجو+رشته» را بشماریم. در این مورد فرمول را به شکل زیر می‌نویسیم.

=COUNTIFS($A$2:$A$7, A2, $B$2:$B$7, B2)

و نتیجه نهایی به‌ شکل زیر در می‌آید.

کاربرد تابع COUNTIFS در کسب و کار

تابع COUNTIFS به‌‌دلیل امکان تعریف چند شرط، در زمان‌هایی که نیاز به تحلیل چند ستون مختلف از داده‌ها باشد، بسیار کاربرد دارد. در دنیای واقعی کسب و کار به‌خصوص زمانی که تحلیل‌گران مالی می‌خواهند پروژه‌ها یا بودجه‌ها را دسته‌بندی کنند، از این تابع استفاده می‌کنند.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

موارد زیر بیشترین کاربرد تابع COUNTIFS در کسب و کارها است.

1. امور مالی و حسابداری
2. مدیریت پروژه و منابع انسانی
3. بازاریابی و فروش
4. تولید و کنترل کیفی محصول
5. فعالیت‌های آموزشی و پژوهشی

برای درک بهتر مطلب، نمونه‌هایی از کاربرد این تابع در هر یک از کسب‌ و کارها را اشاره می‌کنیم.

۱. مالی و حسابداری

کاربردهای زیر نمونه‌هایی از نحوه استفاده تابع COUNTIFS در بخش مالی است.

• شمارش تعداد تراکنش‌های مالی در یک بازه زمانی خاص که مبلغی بیشتر از یک مقدار مشخص دارند.
• شمارش تعداد فاکتورهای که در تاریخ خاص پرداخت شده‌اند.
• شمارش تعداد مشتریانی که در شهری خاص زندگی می‌کنند و خرید بالای مقدار مشخص دارند.

۲. مدیریت پروژه و منابع انسانی

نمونه‌های زیر موارد پر کاربرد استفاده از تابع COUNTIFS در مدیریت پروژه و منابع انسانی است.

• شمارش تعداد پروژه‌های دارای تاخیر که بودجه آن‌ها تمام شده است.
• شمارش تعداد کارمندان بخش فروش که بیش از یک مقدار مشخص کار کرده‌اند.
• شمارش تعداد موقعیت‌های شغلی تمام وقت که حقوقی بالاتر از یک مقدار مشخص دارند.

۳. بازاریابی و فروش

تابع COUNTIFS را می‌توانیم در موارد زیر برای بخش فروش و بازاریابی استفاده کنیم.

• شمارش تعداد فروش یک محصول خاص که در یک ماه مشخص انجام گرفته است.
• شمارش تعداد مشتریانی که هم از کد تخفیف استفاده کرده‌اند و بیش از یک‌بار خرید داشته‌اند.
• شمارش تعداد سفارش‌های بالای یک مقدار مشخص که در روزهای تعطیل انجام شده است.

۴. تولید و کنترل کیفی محصول

نمونه‌های زیر پرکاربردترین موارد استفاده تابع COUNTIFS در حوزه کنترل کیفیت و تولید است.

• شمارش تعداد محصولات معیوب که در یک شیفت کاری مشخص ثبت شده است.
• شمارش تعداد کالاهایی که قیمت بالایی دارند و تاریخ انقضای آن‌ها نزدیک است.
• شمارش تعداد سفارش‌های تحویل داده شده در یک شهر خاص که دارای تاخیر است.

۵. فعالیت‌های آموزشی و پژوهشی

کارکنان دانشگاه، مدارس و پژوهشگران می‌توانند از تابع COUNTIFS در موارد زیر استفاده کنند.

• شمارش تعداد دانش‌آموزانی که نمره بالاتر از یک مقدار مشخص دارند و غیبت آن‌ها از یک عدد معین کمتر است.
• شمارش تعداد شرکت‌کنندگان آزمون که سن بالاتر از یک مقدار مشخص دارند و مدرک خاصی دارند.
• شمارش تعداد پاسخ‌های نظرسنجی در یک جنسیت معین که سنی بالاتر از یک مقدار معین دارند.

جمع‌بندی پایانی

برای شمارش سلول‌ها همراه با چند شرط، از تابع COUNTIFS استفاده می‌کنیم. این تابع تحلیل داده‌های حوزه‌های مختلف کسب و کارها مانند امور مالی، مدیریت پروژه، فروش، بازاریابی و تولید را ساده‌تر می‌کند. در این مطلب از مجله فرادرس دو روش فرمول‌نویسی ساده و حرفه‌ای با تابع COUNTIFS در اکسل را یاد گرفتیم. همچنین با نکات مهم کار با آن نیز آشنا شدیم. با این حال برای کسب مهارت بیشتر در این خصوص و کاهش خطاهای احتمالی هنگام فرمول‌نویسی، علاقه‌مندان می‌توانند به‌عنوان مسیر میانبر یادگیری مجموعه فیلم آموزش توابع و فرمول‌ در اکسل در فرادرس را مشاهده کنند.