تابع SUMIFS در اکسل جمع مقادیر را بر اساس چند شرط محاسبه میکند. این تابع در دسته توابع ریاضی (Math & Trig) قرار دارد و به جمع چند شرطی معروف است. شرطها میتوانند شامل تاریخ، اعداد و متن باشند. تابع SUMIFS برای قسمت شرط، عملگرهای منطقی و کاراکترهای Wild Card هم را پشتیبانی میکند. در آموزش قبل نحوه کار با تابع SUMIF را توضیح دادیم که جمع مقادیر را بر اساس یک شرط محاسبه میکند. حالا وقت آن رسیده است که به سراغ نسخه پیشرفتهتر این تابع، یعنی SUMIFS برویم.
ساختار تابع SUMIFS در اکسل
ساختار تابع SUMIFS به شکل زیر است:
SUMIFS(sum_range, Criteria_range1, criteria1, [criteria_range2, criteria2], …)
۳ آرگومان اول تابع الزامی هستند و آرگومانها و شرطهای دیگر اختیاری هستند.
- sum_range: یک یا چند سلول که باید با یکدیگر جمع شوند. فقط سلولهایی با هم جمع میشوند که حاوی اعداد باشند. سلولهای خالی و متن نادیده گرفته میشوند.
- Criteria_range1: اولین محدودهای که باید توسط شرط مربوط به خود ارزیابی شود.
- Criteria1: اولین شرطی که باید بررسی شود. شرط شما میتواند شامل یک عدد، تاریخ، متن، عملگر منطقی، آدرس سلول، کارکترهای Wild card باشد. :Criteria_range2, Criteria2 محدودهها و شرطهای دیگری که اختیاری هستند. در فرمولهای SUMIFS میتوانید از ۱۲۷ جفت محدوده و شرط استفاده کنید.
نکته مهم: عملکرد SUMIFS با منطق AND کار میکند، به این صورت که اگر تمام شرطها درست باشند سلولهای موجود در sum_range با هم جمع میشوند.
مثال ۱: شرط متنی و عددی در تابع SUMIFS
فرض کنید شما مدیر تولید یک کارخانه تولید مواد شوینده هستید. این کارخانه ۳ نوع محصول (مایع ظرفشویی، مایع دستشویی، مایع سفیدکننده) تولید میکنید و ۴ خط تولید دارد. میخواهید بدانید مجموعا چند لیتر “مایع ظرفشویی” در خط تولید “۲” تولید میشود. یعنی در اینجا دو شرط دارید:
- شرط اول: مایع ظرف شویی
- شرط دوم: خط تولید ۲
برای این کار ابتدا تابع SUMIFS را فرخوانی میکنم. بعد از این کار پنجره Function Arguments باز میشود. در کادر اول یعنی Sum_range کلیک کرده و سلولهای C2 تا C8 را انتخاب میکنم. سپس در کادر Criteria_range کلیک کرده و سلولهای A2 تا A8 را انتخاب میکنم. حالا در کادر Criteria1 کلیک کرده و شرط اول که “مایع ظرف شویی” است را تایپ میکنم. سپس در کادر Criteria_range2 کلیک کرده و سلولهای B2 تا B8 را انتخاب میکنم و در نهایت در کادر Criteria2 کلیک کرده و شرط دوم را که “۲” است وارد میکنم (چون یک عدد است نیاز نیست داخل ” ” قرار دهیم.) در نهایت روی OK کلیک میکنم.
SUMIFS(C2:C8,A2:A8,"مایع ظرف شویی",B2:B8,2)
در مثال قبل، نحوه عملکرد تابع SUMIFS با دو شرط را توضیح دادیم. شما میتوانید از SUMIFS با شرطهای مختلف استفاده کنید و در قسمت شرطها اعداد، تاریخها و عبارات منطقی را استفاده کنید.
مثال ۲: استفاده از عملگرها در تابع SUMIFS
فرض کنید میخواهید مجموع تولید تمام محصولات خط تولید “۲” که مقدار آنها “بیشتر مساوی ۴۰۰۰ لیتر” است را به دست آورید. ابتدا تابع SUMIFS را فراخوانی میکنم و سپس در کادر Sum_range کلیک میکنم و سلولهای C2 تا C8 را انتخاب میکنم. سپس در کادر Criteria_range1 کلیک کرده و سلولهای B2 تا B8 را انتخاب میکنم. بعد از آن در کادر Criteria1 کلیک کرده و شرط اول را که “۲” است تایپ میکنم. سپس در کادر Criteria_range2 کلیک کرده و سلولهای C2 تا C8 را انتخاب میکنم. در نهایت در کادر Criteria 2 کلیک میکنم و شرط دوم که “>=4000” است را وارد میکنم.
SUMIFS(C2:C8,B2:B8,2,C2:C8,">=4000")
دقت کنید که در فرمول SUMIFS، عملگرهای منطقی در ” ” درج شوند.
مثال ۳: استفاده از تاریخ در تابع SUMIFS
در صورتی که میخواهید مقادیر را بر اساس تاریخ جمع کنید، مثلا میخواهید بدانید مجموعا چند لیتر از محصولات مختلف در خط “۱” و در تاریخ “۲۵/۱۲/۹۸” تولید شده است. ابتدا در کادر Sum_range کلیک کرده و سلولهای C2 تا C8 را انتخاب میکنم. سپس محدوده مربوط به شرط اول را که B2 تا B8 است، انتخاب میکنم. حالا در کادر Criteria1 شرط اول را که “۱” است، قرار میدهم. سپس در کادر Criteria_range2 کلیک کرده و سلولهای D2 تا D8 را انتخاب میکنم. در نهایت در کادر Criteria 2 کلیک کرده و شرط دوم را که “۲۵/۱۲/۹۸″&”=” است تایپ میکنم.
SUMIFS(C2:C8,B2:B8,1,D2:D8,"="&"98/12/25")
نکته: وقتی در قسمت شرط از یک عملگر منطقی به همراه یک تاریخ استفاده میکنید، باید علامت “&” را قبل تاریخ قرار دهید. عملگر منطقی و تاریخ را هم بین ” ” قرار دهید.
جمعبندی
در این آموزش تابع SUMIFS را توضیح دادیم و برخی نکات مربوط به آن را توضیح دادیم. توابع اکسل باعث میشوند به سرعت و دقت بالا به نتیجه برسید، پس حتما آنها را به کار بگیرید.
اگر sum_range عدد نباشه و text باشه چجوری میتونیم به جواب برسیم؟
sum_range نمی تواند متن باشد.
سلام
تشکر فراوان خانم زارعی
اگه فرمولهای دیگر مثلا IF رو هم بصورت مثال بزارید خیلی متشکرم
اوقاتی خوشی داشته باشید
سلام. خواهش می کنم. آموزش تابع IF در اکسل – تعیین شرط میتونید این مقاله رو مطالعه بفرمایید. موفق باشید.
با سلام
ممنون از سایت خوبتون
لطفا بفرمایید چطور از تابع sumifs استفاده کنیم در صورتی یک یا چند شرط درست نیست ، مابقی شرط ها را در نظر بگیرد؟
سلام. خواهش می کنم. بین شرط ها or بگذارین.
سلام
من سوالی داشتم
دو شیت در اکسل داریم
شیت اول تشکیل شده از سه پارامتر عنوان و کد حساب و مبلغ می باشدبه عنوان مثال می خواهیم در شیت دوم جمع اعداد شیت اول که عنوان حساب آن ابلاغی است را با کدحساب ۹۹۰۰۱ را مقابل سلی که درآن عنوان ابلاغی درج شده را داشته باشیم چگونه میشود این کار را انجام داد.ممنون از پاسختان
با سلام دو سلول دارم سلول اول در ۱۰ ضرب میشود با سلول دوم جمع به شرطی که سلول دوم از ۱۰ بیشتر نباشد اگه بیشتر باشد مساوی ۱۰ قرار دهد از چه تابعی استفاده کنم
سلام ممنون از وقتی که میزارید.
در مثال ۳ = که نوشتید برای یه تاریخ مشخصه من میخوام جمع روزهای ماه و در انهتای ماه برام بیار
مثلا مایع ظرفشویی در تاریخ های ۹۸/۱۲/۰۲،۹۸/۱۲/۰۴،۹۸/۱۲/۰۵،…. تولید شده
حالا من میخوام جمع این روزها رو در آخر ماه محاسبه کنه ؟؟
ممنونم
باسلام وعرض ادب و خسته نباشید خدمت شما همکار گرامی. من یک فایلی دارم میخام جمع مبالغو بر اساس یک شرط ودر تاریخ های مشخص برام نشون بده از چه فرمولی میتونم استفاده کنم؟
مطالب خیلی عالی بیان شده است. متشکریم مدت ها بود که دنبال یافتن همین فورمول ها بودم تا اینکه کارم سریع تر انجام شود.
سلام. ضمن تشکر از زحمات شما بخصوص شیوه نگارش و آموزش که بسیار عالی است. لطفا” بفرمایید در صورتیکه بخواهیم چند ستون جداگانه را جمع کنیم قسمت Sum_range را چگونه باید بنویسیم ؟