توابع پایه اکسل - تابع SUMIF

تابع SUMIF حاصل‌جمع سلول‌هایی که با معیار و شرط واحدی مطابقت دارند را بر می‌گرداند. معیار را می‌توان به صورت تاریخ، اعداد و متن اعمال کرد. تابع SUMIF از عملگرهای منطقی (>،<،<>،=) و نویسه‌های جایگزین (*،?) برای مطابقت پشتیبانی می‌کند.

     از کجا شروع کنم                                                                                                                                                                

هدف تابع

جمع مقادیرسلول‌های یک محدوده که با یک معیار مطابقت دارند

خروجی تابع

حاصل‌جمع سلول‌هایی که معیاری را برآورده می‌کنند. 

ترکیب تابع

=  SUMIF ( range , criteria , [sum_range] ) 

ورودی تابع (مولفه‌ها‌)

range – محدوده‌ای که معیار روی آن اعمال می‌شود
criteria –شرط و معیاری که باید اعمال شود
sum_range – [اختیاری] محدوده‌ای که باید جمع شود. در صورت حذف ، سلول‌های وارد شده در محدوده مولفه اول با هم جمع می‌شوند.

نحوه استفاده از تابع

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

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

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

ویژگی‌های کلیدی

• مقادیری که با یک شرط و معیار واحد مطابقت دارد را جمع می‌کند.
• با تاریخ، متن و اعداد کار می‌کند.
• از عملگرهای مقایسه‌ای (>، <، <>، =) و نویسه‌های جایگزین (*، ?) پشتیبانی می‌کند.
• در همه نسخه‌های اکسل موجود است 

 برخلاف اکثر توابع اکسل، تابع SUMIF برای مولفه range به یک محدوده معمولی نیاز دارد. اگر سعی کنید از آرایه استفاده کنید، اکسل اجازه ورود فرمول را نمی‌دهد.     

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

مثال پایه

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

= SUMIF ( range , criteria , [sum_range] )

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

مولفه sum_range اختیاری است. اگر این مولفه حذف شود، سلول‌های موجود در محدوده مولفه اول به جای آن جمع می‌شوند.

در برگه زیر، مقدار فروش هر استان را داریم. از SUMIF برای انجام سه محاسبه استفاده می‌کنیم:

 (1) جمع کل فروش‌های ابراهیم محمدی ، (2) جمع کل فروش‌ها در آذربایجان شرقی و (3) جمع کل فروش‌های بالای 83 واحد.

فرمول‌های درج شده در سلول‌های محدوده G3:G5 به این شکل هستند:

= SUMIF ( B3:B13 , "ابراهیم محمدی" , D3:D13 )   //   کل فروش ابراهیم محمدی
= SUMIF ( C3:C13 , "آذربایجان شرقی" , D3:D13 )   //   کل فروش در آذربایجان شرقی
= SUMIF ( D3:D13 , ">83" )   //   جمع فروش‌های بالای 83 واحد

در مورد فرمول‌های فوق به نکات زیر توجه کنید:

• علامت تساوی (=) برای معیارهای "مساوی است با" لازم نیست (یعنی از "ابراهیم محمدی" استفاده کنید نه "ابراهیم محمدی=").
• SUMIF به حروف کوچک و بزرگ لاتین حساس نیست، بنابراین "jim" و "Jim" نتایج یکسانی را بر می‌گردانند.
•  در آخرین فرمول (فرمول سلول G5) مولفه‌سوم حذف شده است ، بنابراین سلول‌های محدوده مشخص شده در مولفه اول جمع می‌شود.
•  اعداد هنگام استفاده با عملگرها باید داخل گیومه ("") قرار گیرند (مثلاً ">100").

توجه داشته باشید که شکل مولفه دوم تابع SUMIF تا حدودی غیرمعمول است. به جای اینکه به سادگی مقدار >100 را به عنوان معیار وارد کنید، باید آن را داخل علامت نقل قول وارد کنید( ">100" ).

اگر مقادیر مورد نیاز را در نقل قول قرار ندهید، اکسل اجازه ورود فرمول را نمی‌دهد.

به ادامه مثال‌ها توجه کنید.

اعمال معیارها

تابع SUMIF از عملگرهای منطقی (>،<،<>،=) و نویسه‌های جایگزین (*،?) برای مطابقت پشتیبانی می‌کند. بخش چالش‌ برانگیز استفاده از تابع SUMIF، شکل ترکیب مورد نیاز برای اعمال معیارها است. دلیل این امر این است که SUMIF جز هشت تابعی است که بررسی شروط را با دو مولفه range و criteria انجام می‌دهند. به دلیل این طراحی، عملگرها باید در داخل نقل قول‌های دوتایی ("") قرار گیرند. جدول زیر نمونه‌هایی از شکل ترکیب معیارهای رایج را نشان می‌دهد:

توجه داشته باشید که دو مثال آخر شامل نویسه الحاق (&) هستند. هر زمان که از مقدار سلول دیگر یا نتیجه فرمولی با عملگر منطقی مانند "<" استفاده می‌کنید، باید آن را پیوند دهید. دلیل این امر آن است که اکسل قبل از اینکه بتواند از مقدار یک عملگر استفاده کند، ابتدا باید آدرس‌های سلول و فرمول‌ها را ارزیابی کند.

معیارهای موجود در سلول دیگر

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

= SUMIF ( range , A1 )

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

= SUMIF ( range , ">" & A1 )

توجه داشته باشید که عملگر "<" را با نویسه الحاق (&) به سلول A1 متصل می‌کنیم. در برگه زیر ، تابع SUMIF طوری پیکربندی شده است که مجموع تمام فروش‌های بالای مقدار موجود در G2 را برگرداند.

توجه داشته باشید که عملگر (<) که متن است، باید داخل علامت نقل قول قرار گیرد. فرمول سلول G3 به صورت زیر وارد شده است:

= SUMIF ( D3:D13 ,  ">" & G2 )   //  است  G2جمع سلول‌هایی که مقدارشان بزرگتر از مقدار سلول

آدرس‌های سلول را داخل علامت نقل قول به صورت "A1" قرار ندهید. انجام این کار آنها را به متن تبدیل می‌کند.

مساوی نیست با

برای بیان معیارهای «مساوی نیست با»، از عملگر « <> » که داخل نماد نقل قول ("") قرار گرفته است، استفاده کنید. برای مثال، همانطور که در برگه زیر مشاهده می‌کنید، از « قرمز <> » برای رنگ‌های «غیرقرمز» و از « آبی <> » برای رنگ‌های «غیرآبی» استفاده شده است:

= SUMIF ( B3:B3 , "<>قرمز", C3:C7 )   //   رنگ‌هایی که "قرمز" نیست
= SUMIF ( B3:B7 , "<>آبی" , C3:C7 )   //   رنگ‌هایی که "آبی" نیست
= SUMIF ( B3:B7 , "<>" & E5 , C3:C7 )   //  برابر نیست E5مقادیری که با مقدار سلول

به موارد زیر توجه کنید:

• در فرمول آخر، مستقیماً از E5 استفاده می‌کنیم، بنابراین باید معیار را به صورت E5 &"<>" وارد کنیم.
• از نماد نقل قول در اطراف آدرس سلول استفاده نکنید.

سلول‌های خالی

SUMIF می‌تواند مجموع را بر اساس خالی یا غیر خالی بودن سلول‌ها محاسبه کند. از ("") برای سلول‌های خالی و از عملگر "نامساوی " ("<>") برای سلول‌هایی که خالی نیست استفاده کنید.

در برگه زیر SUMIF برای جمع مقادیر سلول‌های ستون C بسته به اینکه ستون سلول‌های D حاوی "x" باشد یا خالی ، استفاده می‌شود:

= SUMIF ( D3:D7 , "" , C3:C7 )   // حاصل جمع سلول‌های خالی
= SUMIF ( D3:D7 , "<>" , C3:C7 )   // حاصل جمع سلول‌های غیر خالی

در فرمول دوم، تابع SUMIF مجموع مقادیر سلول‌های غیر خالی را بدون توجه به مقادیر آنها جمع می‌کند. برای دقیق‌تر کردن، می‌توانید از فرمولی به صورت زیر استفاده کنید که تنها مقادیری را جمع می‌کند که سلول مربوطه حاوی "x" باشد:

= SUMIF ( D3:D7 , "x" , C3:C7 )  //  است"x"حاصل جمع سلول‌های که شامل

تاریخ‌ها

در اکسل، تاریخ‌ها اعداد سریالی هستند، بنابراین می‌توانید از عملگرهایی مانند <,>، <=، >= مانند هر عدد دیگری با آنها استفاده کنید. بخش چالش ‌برانگیز استفاده از تاریخ‌ها در شرط‌های SUMIF، وارد کردن تاریخ‌ به روشی است که اکسل آن را بفهمد. مطمئن‌ترین راه برای انجام این کار، ارجاع به یک تاریخ معتبر در سلول دیگر یا استفاده از تابع DATE است. مثال زیر هر دو روش را نشان می‌دهد:

= SUMIF ( B3:B7, " < " & DATE ( 2025,3,1 ) , C3:C7 )
= SUMIF ( B3:B7, " >= " & DATE ( 2025,4,1 ) , C3:C7 )
= SUMIF ( B3:B7 ,">" & E7 , C3:C7 )

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

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

نویسه‌های جایگزین

تابع SUMIF از سه نویسه جایگزین پشتیبانی می‌کند که می‌توانید در معرفی معیارها برای مطابقت بیشتر استفاده کنید:

 نماد ستاره (*) - با صفر یا چند نویسه مطابقت دارد
نماد سوال (?) - با هر نویسه‌ای مطابقت دارد
نماد مد (~) – برای ارزیابی خود نویسه‌های جایگزین استفاده می‌شود

برگه زیر نحوه استفاده از نویسه‌های جایگزین را با تابع SUMIF نشان می‌دهد. فرمول‌های F3:F6 معیارهای شرح داده شده در ستون E را اعمال می‌کنند.

= SUMIF ( B3:B13 , " *ان" , C3:C13 )   //     استان‌هایی که به “ ان”  ختم می‌شود
= SUMIF ( B3:B13 , "*ی" , C3:C13 )   //  استان‌هایی که به “ ی”  اختم می‌شود
= SUMIF ( B3:B13 , "?????" , C3:C13 )   //   استان‌هایی که “5 حرفی” هستند 
= SUMIF ( B3:B13 , "*~*" , C3:C13 )   //  استان‌هایی که به “ * ”  ختم می‌شود

توجه داشته باشید که فرمول سلول F6 از "*~*" برای مطابقت با نماد ستاره (*) که در انتهای "تهران*" در سلول C7 قرار دارد، استفاده می‌کند.

نماد مد (~) امکان می‌دهد خود نویسه جایگزین را پیدا کنید. برای مطابقت با نماد سوال (?)، از "?~"؛ برای تطبیق  نماد ستاره (*)، از "*~"؛ و برای تطبیق نماد مد (~)، از "~~" استفاده کنید.

جدول زیر مثال‌های بیشتری از نحوه استفاده از نویسه‌های جایگزین را نشان می‌دهد:

نکته: نویسه٬های جایگزین فقط با متن کار می‌کنند، نه با اعداد.

منطق OR

تابع SUMIF برای اعمال تنها یک شرط طراحی شده است، بنابراین راهی برای جمع کردن سلول‌ها با منطق "این یا آن" وجود ندارد. یک راه حل این است که معیارها را به عنوان یک آرایه ثابت مانند {"قرمز","آبی"} ارائه داده و سپس فرمول SUMIF را درون تابع SUM به صورت تو در تو قرار دهید:

= SUM ( SUMIF ( range , {"قرمز","آبی"} , sum_range ))   //   قرمز یا آبی

فرمول بالا سلول‌های معرفی شده در مولفه sum_range را زمانی جمع می‌کند که سلول‌های موجود در مولفه اول (range) شامل "قرمز" یا "آبی" باشند. اساسا، SUMIF دو جمع را در یک آرایه بر می‌گرداند (یکی برای "قرمز" و دیگری برای "آبی") و تابع SUM مجموع  این دو جمع ‌را بر می‌گرداند. 

ایجاد جدول خلاصه

می‌توانید از SUMIF برای ایجاد یک جدول خلاصه ساده استفاده کنید. در برگه زیر، لیستی از رنگ‌های غیرتکراری در محدوده F3:F7 داریم. هدف جمع مقادیر ستون D بر اساس رنگ است. فرمول سلول G3، که در سلول‌های پایینی هم کپی شده است  ، به صورت زیر است:

 = SUMIF ( $C$3:$C$14 , F3 , $D$3:$D$14 )

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

اگر از اکسل نسخه ۲۰۲۱ یا بالاتر استفاده می‌کنید، می‌توانید مجموع ستون جدول خلاصه را به طور همزمان با یک فرمول آرایه پویا به صورت زیر محاسبه کنید:

= SUMIF ( C3:C14 , F3:F7 , D3:D14 )

در این حالت به آدرس‌دهی مطلق نیازی نداریم زیرا یک فرمول واحد همه نتایج را ایجاد می‌کند. می‌توانید با استفاده از تابع UNIQUE در سلول F3 یک قدم جلوتر بروید تا لیستی از رنگ‌های غیر تکراری به صورت محدوده آبشاری ایجاد کنید، سپس مستقیما به این محدوده آبشاری به صورت زیر ارجاع دهید: 

= SUMIF ( C3:C14 , F3 # , D3:D14 )

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

در اکسل ۳۶۵، می‌توانید از تابع جدید GROUPBY نیز برای ایجاد جدول خلاصه استفاده کنید.

مشکل کار با آرایه‌ها

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

یکی از محدودیت‌های مهم ‌SUMIF این است که اجازه‌ استفاده از آرایه در مولفه اول را نمی‌دهد. برای درک مطلب، به برگه‌ زیر توجه کنید که در آن 10 تاریخ در ستون B و 10 مقدار در ستون C داریم. هدف ایجاد فرمولی برای  دریافت حاصل‌جمع مقادیر بر اساس سال است. اگر با فرمول‌های اکسل کمی آشنایی دارید، ممکن است فکر کنید که می‌توانید از ترکیب SUMIF و YEAR در یک فرمول به صورت زیر استفاده کنید:

= SUMIF ( YEAR ( B3:B12 )  , E3 , C3:C12 )

ایده این است که سال را از تاریخ‌های ستون B با استفاده از تابع YEAR استخراج کنید و سپس از عدد ۲۰۲۴ در سلول E5 به عنوان معیار استفاده کنید. اگر این روش جواب می‌داد، خیلی خوب می‌شد. اما اکسل حتی اجازه نمی‌دهد این فرمول را وارد کنید. اگر امتحان کنید، با خطای عمومی «مشکلی در فرمول وجود دارد» مواجه خواهید شد.

مشکل این است که SUMIF برای مولفه range به یک محدوده‌ی معمولی نیاز دارد، اما YEAR(B3:B12) آرایه‌ای مانند آرایه زیر را بر می‌گرداند:

{2025;2025;2025;2025;2025;2024;2024;2024;2024;2024}

برای روشن شدن موضوع ذکر کنم که ، استفاده از تابع YEAR به این شکل در اکثر فرمول‌های دیگر به خوبی کار می‌کند. با این حال SUMIF برای مدیریت آرایه‌ها برنامه‌ریزی نشده است، بنابراین کار نخواهد کرد.

 چگونه می‌توانیم این محدودیت را بر طرف کنیم؟

یک راه حل خوب این است که از تابع SUMPRODUCT و فرمولی مانند این استفاده کنید:

 = SUMPRODUCT (sum_range , - -(range=criteria) )

اگر این الگو را با مثال ارائه شده تطبیق دهیم، به نتیجه زیر می‌رسیم:

 = SUMPRODUCT ( C3:C12 , - - ( YEAR ( B3:B12 ) = E3 ) )

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

راه حل فوق مثال خوبی از این است که چگونه SUMPRODUCT می‌تواند یک مشکل پیچیده را در یک فرمول حل کند.

به یاد داشته باشید: اگر سعی کنید یک آرایه برای مولفه اول تابع SUMIF ارائه دهید، نمی‌توانید فرمول را وارد کنید زیرا اکسل پنجره خطای "مشکلی در فرمول وجود دارد" را نمایش می‌دهد که "مشکل آرایه" به صراحت ذکر نشده است.

محدودیت‌ها

تابع SUMIF چند محدودیت دارد که باید از آنها آگاه باشید:

• SUMIF فقط از یک شرط واحد پشتیبانی می‌کند. برای محاسبه حاصل‌جمع سلول‌هایی که با چند شرط مطابقت دارند، از تابع SUMIFS استفاده کنید.
• SUMIF به حروف کوچک و بزرگ لاتین حساس نیست. از تابع EXACT به همراه SUMPRODUCT برای ایجاد یک جمع حساس به حروف بزرگ و کوچک لاتین استفاده کنید.
• SUMIF به یک محدوده معمولی برای مولفه اول خود نیاز دارد. محدوده نمی‌تواند به صورت آرایه باشد. این بدان معناست که نمی‌توانید مقادیر یک محدوده داخل فرمول را قبل از اعمال معیارها تغییر دهید. 
•  اگر با استفاده از تابع SUMIF به محدوده‌ای در یک فایل اکسل دیگر ارجاع دهید، فایل اکسل باید باز باشد، در غیر این صورت SUMIF خطای !VALUE# را بر می‌گرداند. برای حل این مشکل، می‌توانید از تابع SUMPRODUCT استفاده کنید که این محدودیت را ندارد. 
• تابع SUMIF فرض می‌کند که تعداد سلول‌های محدوده معرفی شده در مولفه سوم (sum_range ) برابر سلول‌های محدوده معرفی شده در مولفه اول (range) است و در صورت لزوم، sum_range را بی‌صدا تغییر اندازه می‌دهد تا با مولفه اول مطابقت داشته باشد و از سلول سمت چپ بالای محدوده به عنوان مبدا استفاده می‌کند. این محدودیت می‌تواند نتایج نادرستی ارائه دهد که "عادی" به نظر می‌رسد.

رایج‌ترین روش برای دور زدن اکثر این محدودیت‌ها، استفاده از تابع SUMPRODUCT است. با این حال، نسخه‌‌های جدید اکسل جایگزین‌های قدرتمندی از جمله BYROW، BYCOL و GROUPBY ارائه می‌دهد.

نکات

• SUMIF فقط از یک شرط پشتیبانی می‌کند. برای اعمال چند شرط از تابع SUMIFS استفاده کنید.
• هنگامی که مولفه sum_range حذف شود، سلول‌های محدوده مولفه اول جمع می‌شوند.
• معیارهای غیر عددی باید داخل علامت نقل قول قرار گیرند (مثلاً "<100"، ">32"، "TX")
• آدرس‌های سلول استفاده شده در مولفه دوم داخل علامت نقل قول قرار نمی‌گیرند، مثلاً "<"&A1
• نویسه‌های جایگزین را می‌توان در معیارها استفاده کرد.
• SUMIF به یک محدوده معمولی نیاز دارد و نمی‌توانید از محدوده به صورت آرایه استفاده کنید.

لطفا دیدگاه خود را در کادر پایین با ما به اشتراک بگذارید.

آیا می‌خواهید:

• برنامه‌های قدرتمند و انعطاف پذیر با اکسل ایجاد کنید.
• فرصت های شغلی جدید کسب کنید.

کتاب‌های معرفی شده ، راهنمای گام به گام جامع و عملی برای افرادی است که (مانند شما) می‌خواهند به این اهداف برسند.