شمارش و جمع شرطی: COUNTIFS، SUMIFS، AVERAGEIFS، MAXIFS و SUMPRODUCT

گزارش‌گیری بدون فیلتر دستی

سؤال‌هایی مثل «فروش نماینده‌ی کاشان در مرداد چقدر بود؟» یا «چند فاکتور بالای ۱۰۰ میلیون تومان داریم؟» پاسخشان خانواده‌ی توابع شرطی است. نسخه‌ی جمع (S دار) از چند شرط هم‌زمان پشتیبانی می‌کند و بهتر است همیشه همان را به کار ببرید، حتی برای یک شرط.

ساختار

=COUNTIFS(محدوده_شرط1, شرط1, محدوده_شرط2, شرط2, …)
=SUMIFS(محدوده_جمع, محدوده_شرط1, شرط1, …)          ← محدوده‌ی جمع اول می‌آید
=AVERAGEIFS(محدوده_میانگین, محدوده_شرط1, شرط1, …)
=MAXIFS(محدوده_بیشینه, محدوده_شرط1, شرط1, …)        ← Excel 2019 به بعد

فرض: A=تاریخ، B=نماینده، C=استان، D=طرح، E=مبلغ
=SUMIFS(E:E, C:C, "اصفهان")
=SUMIFS(E:E, C:C, H2, A:A, ">="&H3, A:A, "<="&H4)     ← بازه‌ی تاریخ از سلول
=COUNTIFS(E:E, ">=1E8")                                  ← فاکتورهای بالای 100 میلیون
=COUNTIFS(D:D, "*ماهی*")                                  ← طرح‌هایی که «ماهی» دارند
=COUNTIFS(B:B, "<>")                                     ← سلول‌های غیرخالی
=AVERAGEIFS(E:E, B:B, "رضا کریمی", E:E, ">0")
=MAXIFS(A:A, B:B, "رضا کریمی")                            ← آخرین تاریخ فروش هر نماینده

قواعد نوشتن شرط

منطق «یا» و شرط‌های پیچیده

شرط‌های SUMIFS همیشه با AND ترکیب می‌شوند. برای OR، آرایه‌ای از مقادیر بدهید و نتیجه را جمع بزنید. برای شرطی روی خود داده (مثل ماه تاریخ) که SUMIFS نمی‌پذیرد، سراغ SUMPRODUCT بروید:

=SUM(SUMIFS(E:E, C:C, {"تهران","قم","کرج"}))

=SUMPRODUCT((MONTH(A2:A5000)=5) * (C2:C5000="کاشان") * E2:E5000)
=SUMPRODUCT(--(TEXT(A2:A5000,"[$-fa-IR,16]yyyy-mm")="1403-05"), E2:E5000)

در Excel 365 می‌توانید همین فرمول را با SUM هم بنویسید چون آرایه‌ها بومی‌اند؛ SUMPRODUCT برای سازگاری با نسخه‌های قدیمی می‌ماند. دقت کنید در SUMPRODUCT از محدوده‌ی محدود (مثل A2:A5000) استفاده کنید، نه کل ستون؛ کل ستون یعنی یک میلیون سطر محاسبه برای هر فرمول.

نکته‌هایی که کمتر کسی می‌داند

• COUNTIF متن‌های عددی را به عدد تبدیل می‌کند و فقط ۱۵ رقم را می‌بیند؛ دو شماره‌کارت ۱۶رقمی که فقط رقم آخرشان فرق دارد «تکراری» شمرده می‌شوند. راه‌حل: =COUNTIF(A:A, A2&"*") که مقایسه را متنی می‌کند.
• محدوده‌های SUMIFS باید «محدوده» باشند نه آرایه‌ی محاسبه‌شده؛ SUMIFS(E:E, MONTH(A:A), 5) اصلاً قابل ورود نیست. این دقیقاً جایی است که SUMPRODUCT یا ستون کمکی لازم می‌شود.
• SUMIFS با ارجاع کل ستون (E:E) سریع است چون فقط تا آخرین سلول استفاده‌شده را می‌خواند؛ این بهینه‌سازی برای SUMPRODUCT وجود ندارد.
• شرط "=" (فقط علامت مساوی) سلول‌های واقعاً خالی را می‌شمارد اما "" سلول‌هایی را هم که فرمولشان رشته‌ی خالی برمی‌گرداند؛ تفاوتی که گزارش شمارش را چند واحد جابه‌جا می‌کند.
• اگر محدوده‌ها هم‌اندازه نباشند (E2:E100 و C2:C99) SUMIFS خطای #VALUE! می‌دهد، اما SUMIF قدیمی بی‌صدا محدوده را تطبیق می‌دهد و نتیجه‌ی غلط تولید می‌کند.