تابع SUMIFS مجموع سلولهایی که با چند معیار و شرط مطابقت دارند را بر میگرداند. برای تعریف معیارها تابع SUMIF از عملگرهای منطقی (>،<،<>،=) و نویسههای جایگزین (*،?)پشتیبانی میکند و میتواند از سلولهایی که حاوی تاریخ، اعداد و متن هستند، استفاده کند.
هدف تابع
جمع مقادیر سلولهای یک محدوده که با چند معیار مطابقت دارند.
خروجی تابع
حاصلجمع سلولهایی که تمام معیارها را برآورده میکنند.
ترکیب عمومی تابع
= SUMIFS ( sum_range , range1 , criteria1 , [range2] , [criteria2] , ...)
ورودی تابع (مولفهها)
sum_range –محدودهای که باید جمع شود.
Range1 – اولین محدودهای که باید ارزیابی شود
Criteria1 –معیارهایی که باید روی محدوده مشخص شده در مولفه Range1 استفاده شوند.
Range2 – [اختیاری] محدوده دوم که باید ارزیابی شود
Criteria2 – [اختیاری] معیارهایی که باید روی محدوده مشخص شده در مولفه Range2 استفاده شوند.
نحوه استفاده از تابع
تابع SUMIFS اعداد را در صورت برآورده شدن یک یا چند شرط جمع میکند. این تابع یکی از پرکاربردترین توابع اکسل است و آن را در فایلهای اکسلی که مجموع مقادیر دادهها را بر اساس تاریخ، متن یا اعداد محاسبه میکنند، خواهید دید. SUMIFS در عین قدرتمند بودن، دارای یک ترکیب منحصر به فرد است که شرطهای منطقی را به دو قسمت تقسیم و آن را از بسیاری از توابع دیگر اکسل متفاوت میکند. در نتیجه ، تعریف معیارها در SUMIFS میتواند کمی پیچیده باشد.
همچنین توجه داشته باشید که SUMIFS از منطق تابع AND استفاده میکند. اعداد برای اینکه در نتیجه حاصل جمع گنجانده شوند، باید با تمام شرطها مطابقت داشته باشد.
ویژگیهای کلیدی
- مقادیری که با یک یا چند شرط و معیار مطابقت دارند ، جمع میکند.
- هر شرط جدید به یک محدوده و معیار جداگانه نیاز دارد.
- با تاریخ، متن و اعداد کار میکند.
- از عملگرهای مقایسهای (>، <، <>، =) و نویسههای جایگزین (*، ?) پشتیبانی میکند.
- برای اینکه مقدار مربوطه در حاصل جمع نهایی گنجانده شود، همه معیارهای معرفی شده باید درست باشند.
- همه محدودهها باید اندازه یکسانی داشته باشند، در غیر این صورت SUMIFS خطای !VALUE# را بر میگرداند.
- در اکسل 2007 و نسخههای بالاتر موجود است
برخلاف اکثر توابع اکسل ، محدوده های معرفی شده به تابع SUMIFS بایستی به صورت محدوده معمولی باشد. اگر سعی کنید از آرایه استفاده کنید، اکسل اجازه ورود فرمول را نمیدهد.
ترکیب تابع
ترکیب تابع SUMIFS به معیارهای مورد ارزیابی بستگی دارد. هر شرط با یک محدوده و معیار جداگانه ارائه میشود. ترکیب مولفههای SUMIFS به این شکل است:
= SUMIFS ( sum_range , range1 ,criteria1 ) // تابع با یک شرط
= SUMIFS ( sum_range , range1 , criteria1 , range2 , criteria2 ) // تابع با دو شرط
توجه داشته باشید که مولفه sum_range همیشه اولین مولفه است. این مولفه محدوده سلولها برای جمع کردن است. هر شرط به عنوان یک جفت مولفه به صورت محدوده/معیار ، ارائه میشود. فرمول اول یک شرط و فرمول دوم دو شرط را تعریف میکند. شرطهای اضافی توسط جفتهای محدوده/معیار اضافی تعریف میشوند.
مثال پایه
برگه زیر شامل دادههای ساده فروش است. از SUMIFS برای انجام سه محاسبه زیر استفاده میکنیم:
1. حاصلجمع فروشهایی که رنگ آنها قرمز
2. حاصلجمع فروشهایی که رنگ آنها قرمز و استان آنها تهران است
3. حاصلجمع فروشهایی که رنگ آنها قرمز، استان آنها تهران و مقدار آنها >80 باشد
فرمولهای درج شده در سلولهای محدوده I3:I5 به این شکل هستند:
= SUMIFS ( F3:F13 , C3:C13 , "قرمز" ) // قرمز
= SUMIFS ( F3:F13 , C3:C13 , "قرمز" , D3:D13 , "تهران" ) // قرمز و تهران
= SUMIFS ( F3:F13 , C3:C13 , "قرمز" , D3:D13 , "تهران" , F3:F13 , ">80" ) // >80 قرمز و تهران و
در رابطه با فرمولهای فوق به نکات زیر توجه کنید:
- علامت تساوی (=) برای معیارهای "مساوی است با" لازم نیست (یعنی از "قرمز" استفاده کنید نه "= قرمز").
- SUMIFS به حروف کوچک و بزرگ لاتین حساس نیست، بنابراین "jim" و "Jim" نتایج یکسانی را بر میگرداند.
- SUMIFS در ارزیابی معیارها همیشه از منطق تابع AND استفاده میکند. همه معیارها باید برقرار باشد.
- اعداد هنگام استفاده با عملگرها باید داخل گیومه ("") قرار گیرند (مثلاً ">100").
توجه داشته باشید که شکل مولفههای مربوط به اعمال معیارها در تابع SUMIFS تا حدودی غیرمعمول است. به جای اینکه به سادگی مقدار >80 را به عنوان معیار وارد کنید، باید آن را داخل علامت نقل قول وارد کنید( ">80" ). اگر مقادیر مورد نیاز را در نقل قول قرار ندهید، اکسل اجازه ورود فرمول را نمیدهد.
اعمال معیارها
تابع SUMIFS از عملگرهای منطقی (>،<،<>،=) و نویسههای جایگزین (*،?) برای مطابقت با معیارها پشتیبانی میکند. بخش دشوار، شکل ترکیب مورد نیاز برای اعمال معیارها است. دلیل این امر این است که SUMIFS جز هشت تابعی است که بررسی شروط را با مولفههای range و criteria انجام میدهند. به دلیل این طراحی ، عملگرها باید در داخل نقل قولهای دوتایی ("") قرار گیرند. جدول زیر نمونههایی از شکل ترکیب مورد نیاز برای انواع معیارها را نشان میدهد:
توجه داشته باشید که دو مثال آخر شامل نویسه الحاق (&) هستند. هر زمان که از مقدار سلول دیگر یا نتیجه فرمولی با عملگر منطقی مانند "<" استفاده میکنید، باید آن را پیوند دهید. دلیل این امر آن است که اکسل قبل از اینکه بتواند از مقدار یک عملگر استفاده کند، ابتدا باید آدرسهای سلول و فرمولها را ارزیابی کند.
معیارهای موجود در سلول دیگر
برای استفاده از SUMIFS اغلب راحت است که معیارها را در سلول دیگری قرار دهید و سپس در فرمول خود به آن سلول ارجاع دهید. این کار تغییر معیارها را در صورت نیاز بدون ویرایش فرمول اصلی آسان میکند. به عنوان مثال ، میتوانید سلولهای محدودهای را که مقادیر آنها برابر با مقدار سلول A1 هستند، به این صورت جمع کنید:
= SUMIF ( sum_range , range , A1 )
اگر میخواهید یک عملگر را در معیار مورد نظر بگنجانید (معیار به عملگر منطقی نیاز دارد)، باید آدرس سلول را به عملگر الحاق کنید. برای مثال، برای حاصلجمع سلولهای یک محدوده که مقدار آنها بزرگتر از مقدار سلول A1 هستند، از فرمولی به صورت زیر استفاده کنید:
= SUMIF ( sum_range , range , ">" & A1 )
توجه داشته باشید که عملگر "<" را با نویسه الحاق (&) به سلول A1 متصل میکنیم. در برگه زیر ، تابع SUMIFS طوری پیکربندی شده است که مجموع تمام فروشهای بیشتر از مقدار موجود در G2 را برگرداند. توجه داشته باشید ، عملگر (<) که متن است ، باید داخل علامت نقل قول قرار گیرد. فرمول سلول G3 به صورت زیر وارد شده است:
= SUMIF ( D3:D13 , D3:D13 , ">" & G2) // است G2 جمع سلولهایی که بزرگتر از مقدار سلول
آدرسهای سلول را داخل علامت نقل قول به صورت "A1" قرار ندهید. انجام این کار آنها را به متن تبدیل میکند.
مساوی نیست با
برای بیان معیارهای «مساوی نیست با»، از عملگر « <> » که داخل نماد نقل قول ("") قرار گرفته است، استفاده کنید. برای مثال، همانطور که در برگه زیر مشاهده میکنید، از « قرمز <> » برای رنگهای «قرمز نیست» و از « آبی <> » برای رنگهای «آبی نیست» استفاده کنید:
= SUMIFS ( C3:C7 , B3:B7 , "<>قرمز") // رنگهایی که "قرمز" نیست
= SUMIFS ( C3:C7 , B3:B7 , "<>آبی") // رنگهایی که "آبی" نیست
= SUMIFS ( C3:C7 , B3:B7 , "<>" & E5) // برابر نیست E5 مقادیری که با مقدار سلول
به موارد زیر توجه کنید:
- در فرمول آخر، مستقیماً از E5 استفاده میکنیم، بنابراین باید معیار را به صورت E5 &"<>" وارد کنیم.
- از نماد نقل قول در اطراف آدرس سلول استفاده نکنید.
سلولهای خالی
SUMIFS میتواند حاصلجمع را بر اساس خالی یا غیر خالی بودن سلولها محاسبه کند. از ("") برای سلولهای خالی و از عملگر نامساوی ("<>") برای سلولهایی که خالی نیست استفاده کنید. در برگه زیر SUMIFS برای جمع کردن مقادیر سلولهای ستون C بسته به اینکه ستون سلولهای D حاوی "x" باشد یا خالی ، استفاده شده است:
= SUMIFS ( C3:C7 , D3:D7 , "") // حاصل جمع سلولهای خالی
= SUMIFS ( C3:C7 , D3:D7 , "<>") // حاصل جمع سلولهای غیر خالی
در فرمول دوم، تابع SUMIFS مجموع مقادیر سلولهای غیر خالی را بدون توجه به مقادیر آنها جمع میکند. برای دقیقتر کردن، میتوانید از فرمولی به صورت زیر استفاده کنید که تنها مقادیری را جمع میکند که سلول مربوطه حاوی "x" باشد:
= SUMIFS ( C3:C7 , D3:D7, "x") // است"x"حاصل جمع سلولهای که شامل
تاریخها
در اکسل، تاریخها اعداد سریالی هستند، بنابراین میتوانید از عملگرهایی مانند <,>، <=، >= با آنها مانند هر عدد دیگری استفاده کنید. بخش چالش برانگیز استفاده از تاریخها در معیارهای SUMIFS وارد کردن تاریخ به روشی است که اکسل آن را بفهمد. مطمئنترین راه برای انجام این کار، ارجاع به یک تاریخ معتبر در سلول دیگر یا استفاده از تابع DATE است. مثال زیر هر دو روش را نشان میدهد:
= SUMIFS ( C3:C10, B3:B10 , " < " & DATE ( 2025 , 3 , 1 ) )
= SUMIFS ( C3:C10, B3:B10 , " >= " & DATE ( 2025 , 4 , 1 ) )
= SUMIFS ( C3:C10 , B3:B10 , ">" & E8)
= SUMIFS ( C3:C10 , B3:B10 , ">=" & DATE ( 2025 , 5 , 1 ) , B3:B10 , "<=" & DATE ( 2025 , 5 , 31 ) )
به موارد زیر توجه کنید:
- هنگام استفاده از آدرس سلول ، باید آدرس را به عملگر مورد نظر به صورت ">"&E10 متصل کنید.
- به طور کلی، بهتر است از درج تاریخ در فرمول خودداری کنید و به جای آن به تاریخ در سلول دیگری ارجاع دهید.
- ارجاع به تاریخ در سلول دیگر، تغییر تاریخ را بدون ویرایش فرمول آسان میکند.
نکته حرفهای: از درج تاریخ در فرمول به صورت "هارد کد" خودداری کنید. به جای آن ، تاریخ را در یک سلول دیگر قرار دهید، سپس در فرمول خود به آن سلول ارجاع دهید. با انجام این کار میتوانید به راحتی تاریخ مورد استفاده را ببینید و در صورت نیاز بدون ویرایش فرمول ، آن را تغییر دهید.
نویسههای جایگزین
تابع SUMIFS از سه نویسه جایگزین پشتیبانی میکند که میتوانید در معرفی معیارها برای مطابقت بیشتر استفاده کنید:
- نماد ستاره (*) - با صفر یا چند نویسه مطابقت دارد
- نماد سوال (?) - با هر نویسهای مطابقت دارد
- نماد مد (~) – برای ارزیابی خود نویسههای جایگزین استفاده میشود
برگه زیر نحوه استفاده از نویسههای جایگزین را با تابع SUMIFS نشان میدهد. فرمولهای F3:F6 معیارهای شرح داده شده در ستون E را اعمال میکنند.
= SUMIFS ( C3:C13 , B3:B13 , " *ان") // استانهایی که به “ ان” ختم میشود
= SUMIFS ( C3:C13 , B3:B13 , "*ی") // استانهایی که به “ ی” ختم میشوده
= SUMIFS ( C3:C13 , B3:B13 , "?????") // استانهایی که به “5 حرفی” هستند
= SUMIFS ( C3:C13 , B3:B13 , "*~*") // استانهایی که به “ * ” ختم میشود
توجه داشته باشید که فرمول سلول F6 از "*~*" برای مطابقت با نماد ستاره (*) که در انتهای "تهران*" در سلول C7 قرار دارد، استفاده میکند.
نماد مد (~) امکان میدهد خود نویسه جایگزین را پیدا کنید. برای مطابقت با نماد سوال (?)، از "?~"؛ برای تطبیق نماد ستاره (*)، از "*~"؛ و برای تطبیق نماد مد (~)، از "~~" استفاده کنید. جدول زیر مثالهای بیشتری از نحوه استفاده از نویسههای جایگزین را نشان میدهد:
نکته: نویسه٬های جایگزین فقط با متن کار میکنند، نه با اعداد.
منطق OR
تابع SUMIFS برای اعمال چند شرط با منطق تابع AND طراحی شده است، بنابراین روش مشخصی برای جمع سلولها با "منطق تابع OR" وجود ندارد. با این حال ، یک راه حل میتواند این باشد که معیارها را به عنوان یک آرایه ثابت مانند {"قرمز","آبی"} ارائه داده و سپس فرمول SUMIFS را درون تابع SUM به صورت تو در تو قرار دهید:
= SUM ( SUMIFS ( sum_range , range , {"قرمز","آبی"} ) ) // قرمز یا آبی
فرمول فوق سلولهای معرفی شده در مولفه sum_range را زمانی جمع میکند که سلولهای موجود در مولفه دوم (range) شامل "قرمز" یا "آبی" باشد. اساسا، SUMIFS دو جمع را در یک آرایه بر میگرداند (یکی برای "قرمز" و دیگری برای "آبی") و تابع SUM مجموع این دو حاصلجمع را بر میگرداند.
جدول خلاصه
میتوانید از SUMIFS برای ایجاد یک جدول خلاصه ساده استفاده کنید. در برگه زیر، لیستی از رنگهای غیرتکراری در محدوده F3:F7 داریم. هدف جمع مقادیر ستون D بر اساس رنگ است. فرمول سلول G3، که در سلولهای پایینی هم کپی شده است ، به صورت زیر است:
= SUMIFS ($D$3:$D$14 , $C$3:$C$14 , F3)
توجه داشته باشید که مولفههای اول و دوم به صورت آدرس مطلق قفل شدهاند تا از تغییر آدرس سلولهای فرمول هنگام کپی فرمول در سلولهای دیگر جلوگیری شود. اگر از اکسل نسخه ۲۰۲۱ یا بالاتر استفاده میکنید، میتوانید مجموع ستون جدول خلاصه را به طور همزمان با یک فرمول آرایه پویا به صورت زیر محاسبه کنید:
= SUMIFS ( D3:D14 , C3:C14 , F3:F7 )
در این حالت به آدرسدهی مطلق نیازی نداریم زیرا این فرمول همه نتایج را ایجاد میکند. میتوانید با استفاده از تابع UNIQUE در سلول F3 یک قدم جلوتر بروید تا لیستی از رنگهای غیر تکراری به صورت محدوده آبشاری ایجاد کنید، سپس مستقیما به این محدوده آبشاری به صورت زیر ارجاع دهید:
= SUMIFS ( D3:D14 , C3:C14 , F3 #)
مزیت استفاده از محدوده آبشاری این است که چنانچه لیست رنگها تغییر کند، حاصلجمعها بهروزرسانی میشوند.
در اکسل ۳۶۵، میتوانید از تابع جدید GROUPBY نیز برای ایجاد جدول خلاصه استفاده کنید.
مشکل کار با آرایهها
توجه: برای موارد استفاده پیشرفته از تابع ، این مشکل یک محدودیت مهم در کاری است که SUMIFS میتواند انجام دهد.
یکی از محدودیتهای مهم SUMIF این است که اجازه استفاده از آرایه در مولفه range را نمیدهد. برای درک مشکل ، به برگه زیر توجه کنید که در آن 10 تاریخ در ستون B و 10 مقدار در ستون C داریم. هدف ایجاد فرمولی برای دریافت حاصلجمع مقادیر بر اساس سال است. میتوانیم این کار را با فرمول SUMIFS زیر انجام دهیم:
= SUMIFS ( C3:C12 , B3:B12 , ">=" & DATE ( E3, 1 ,1 ) , B3:B12 , "<=" & DATE ( E3, 12 , 31) )
نکته: معمولاً در فرمولها برای آدرسدهی از آدرس مطلق استفاده میکنم تا فرمول بدون تغییر کپی شود، اما در فرمول فوق آدرسها را نسبی گذاشتهام تا خواندن آنها آسانتر باشد.
این فرمول به خوبی کار میکند، اما کمی پیچیده است. اگر با فرمولهای اکسل کمی آشنایی داشته باشید، ممکن است فکر کنید که میتوانید از ترکیب SUMIFS و YEAR با هم در یک فرمول هوشمندانه به صورت زیر استفاده کنید:
= SUMIFS ( C3:C12 , YEAR ( B3:B12 ) ,E3 )
ایده این است که سال را از تاریخهای ستون B با استفاده از تابع YEAR استخراج و سپس از مقدار ۲۰۲۴ (در سلول E3) برای معیار استفاده میکنیم. اگر این روش جواب میداد، خیلی خوب میشد. اما ، اکسل حتی اجازه نمیدهد این فرمول را وارد کنید. اگر امتحان کنید، با خطای عمومی "مشکلی در فرمول وجود دارد" مواجه خواهید شد:
مشکل این است که SUMIFS برای مولفه range به یک محدودهی مناسب نیاز دارد، اما YEAR(B3:B12) آرایهای مانند آرایه زیر را بر میگرداند:
{2025;2025;2025;2025;2025;2024;2024;2024;2024;2024}
برای روشن شدن موضوع ذکر کنم که ، استفاده از تابع YEAR به این شکل در اکثر فرمولهای دیگر به خوبی کار میکند. با این حال SUMIFS برای مدیریت آرایهها برنامهریزی نشده است، بنابراین کار نخواهد کرد.
چگونه میتوانیم این مشکل را حل کنیم؟
یک راه حل استفاده از تابع SUMPRODUCT با فرمولی به صورت زیر است:
= SUMPRODUCT ( sum_range , - - (range = criteria) )
اگر این الگو را با مثال ارائه شده تطبیق دهیم، به نتیجه زیر میرسیم:
= SUMPRODUCT ( C3:C12 , - - ( YEAR ( B3:B12 ) = E3 ) )
این فرمول سادهتر از فرمول SUMIFS فوق است. راه حل فوق مثال خوبی از این است که چگونه SUMPRODUCT میتواند یک مشکل پیچیده را حل کند.
به خاطر داشته باشید: اگر سعی کنید یک آرایه برای محدوده ارائه دهید، نمیتوانید فرمول را وارد کنید زیرا اکسل پنجرهی خطای «مشکلی در فرمول وجود دارد» را نمایش میدهد که «مشکل آرایه» به صراحت ذکر نشده است.
محدودیتها
تابع SUMIFS چند محدودیت دارد که باید از آنها آگاه باشید:
- شرطها در SUMIFS با منطق تابع AND به هم متصل میشوند. به عبارت دیگر، برای اینکه مقدار یک سلول در حاصلجمع قرار گیرد، بایستی همه شرطها برای آن مقدار درست باشد.
- همه محدودهها باید اندازه یکسانی داشته باشند. اگر محدودههایی را ارائه دهید که مطابقت ندارند، با خطای !VALUE# مواجه خواهید شد.
- تابع SUMIFS برای همه مولفههای range به محدودههای معمولی نیاز دارد؛ نمیتوانید از یک آرایه استفاده کنید. این بدان معناست که نمیتوانید کارهایی مانند استخراج سال از تاریخها را در داخل تابع SUMIFS انجام دهید. برای تغییر مقادیری که قبل از اعمال معیارها در مولفه range ظاهر میشود، تابع SUMPRODUCT یک راه حل انعطافپذیر است.
- SUMIFS به حروف کوچک و بزرگ لاتین حساس نیست. برای جمع کردن مقادیر بر اساس یک شرط حساس به حروف کوچک و بزرگ ، میتوانید از فرمولی بر اساس ترکیب تابع SUMPRODUCT با تابع EXACT استفاده کنید.
- اگر به یک محدوده در یک فایل اکسل دوم ارجاع دهید، تابع SUMIFS برای محاسبه نتیجه، نیاز دارد که فایل اکسل باز باشد. اگر فایل اکسل خارجی باز نباشد، خطای !VALUE# را مشاهده خواهید کرد. به عنوان یک راه حل ، میتوانید از تابع SUMPRODUCT استفاده کنید.
رایجترین روش برای دور زدن اکثر این محدودیتها، استفاده از تابع SUMPRODUCT است.
نکات
- SUMIFS از چند شرط با استفاده از منطق تابع AND پشتیبانی میکند.
- همه محدودهها باید اندازه یکسانی داشته باشند. اگر محدودههایی را ارائه دهید که مطابقت ندارند، خطای !VALUE# دریافت خواهید کرد.
- معیارهای غیر عددی باید داخل علامت نقل قول قرار گیرند (مثلاً "<100"، ">32"، "TX")
- آدرسهای سلول استفاده شده در معیارها داخل علامت نقل قول قرار نمیگیرند، مثلاً "<"&A1
- نویسههای جایگزین را میتوان در معیارها استفاده کرد.
- SUMIF به یک محدوده معمولی نیاز دارد و نمیتوانید از محدوده به صورت آرایه استفاده کنید.
لطفا دیدگاه خود را در کادر پایین با ما به اشتراک بگذارید.
آیا میخواهید:
- برنامههای قدرتمند و انعطاف پذیر با اکسل ایجاد کنید.
- فرصت های شغلی جدید کسب کنید.
کتابهای معرفی شده ، راهنمای گام به گام جامع و عملی برای افرادی است که (مانند شما) میخواهند به این اهداف برسند.
