تابع Sumif اکسل | محاسبه جمع شرطی در یک مجموعه داده
آموزش تابع Sumif اکسل
فرض کنید در یک بانک اطلاعاتی می خواهیم جمع فروش یک محصول خاص را استخراج کنیم. یا جمع ساعات مرخصی یک کارمند را از بین لیست مرخصی ها محاسبه کنیم. همانطور که تا الان متوجه شده اید، در واقع عمل جمع را میخواهیم به یک شرط معطوف کنیم. این موضوع در اکسل بسیار پر استفاده است و تابع Sumif اکسل (برای یک شرط) و Sumifs (برای بیش از یک شرط) برای این مسئله اختصاص داده شده است. مثلا اگر بخواهیم جمع فروش یک محصول را در یک تاریخ خاص محاسبه کنیم، باید از Sumifs استفاده کنیم چرا که دو شرط داریم: یکی محصول و دیگری تاریخ مورد نظر.
در این دو تابع، نکته ای که اهمیت دارد این است که بتوانیم شروط و محدوده های مربوط به آنها را به درستی تشخیص دهیم
در ادامه آرگومان های تابع Sumif را تشریح میکنیم:
Range: محدوده ای که شرط مورد نظر ما در آن وجود دارد.
Criteria: شرط مورد نظر.
[Sum_Range]: محدوده ای که عمل جمع بر روی آن انجام می شود. این آرگومان اختیاری است و زمانی که Range و Sum_Range مشترک است، می توانیم آن را در فرمول وارد نکنیم.
با ذکر چند مثال این تابع را شرح می دهیم:
مثال ۱: بانک اطلاعاتی مربوط به فروش محصولات مختلف و مبالغ فروش در تاریخ های مختلف موجود است. می خواهیم جمع فروش محصول ۲ محاسبه کنیم. طبق شکل ۱ تابع Sumif را می نویسیم.
شکل ۱- تابع Sumif اکسل – نحوه ثبت تابع sumif
آرگومان اول: ستونی است که شرط ما در آن وجود دارد. ستون نام محصول یا محدوده A2:A20 انتخاب می شود.
=SUMIF(A2:A20,E2,B2:B20)
آرگومان دوم: شرط ما، یعنی کلمه محصول۲ است. که هم می توان به سل ارجاع داد و هم مستقیم در تابع نوشت. به این صورت: “محصول۲”
=SUMIF(A2:A20,E2,B2:B20)
آرگومان سوم: ستونی که عملیات جمع بر روی آن انجام می شود. ستون میزان فروش یا محدوده B2:B20 انتخاب می شود.
=SUMIF(A2:A20,E2,B2:B20)
محدوده های Range و Sum range حتما باید هم اندازه و هم تراز باشند. یعنی از یک ردیف شروع شده و به یک ردیف ختم شود.
مثال ۲: در بالا گفتیم که آرگومان سوم تابع Sumif اختیاری است و می تواند در فرمول وجود نداشته باشد. به مثال زیر دقت کنید.
می خواهیم ببینیم جمع فروش های بیش از ۴۰۰۰۰ چقدر است. در این حالت فرمول به شرح زیر تغییر میکند:
=SUMIF(B2:B20,”>40000”)
در شکل ۲ مشاهده میکنید که آرگومان Sum-Range حذف شده است. چرا که عمل جمع قرار است روی همان محدوده شرط اعمال شود. پس می تواند حذف شود.
شکل ۲- تابع Sumif اکسل – بدون آرگومان اختیاری
حالا می خواهیم جمع فروش محصول ۲ را در تاریخ های معینی محاسبه کنیم. با توجه به اینکه بیش از یک شرط داریم، باید از Sumifs استفاده کنیم. حتما آموزش مربوط به Sumifs، که نکات کاربردی را در بر می گیرد را مطالعه کنید.
با سلام
جدول دیتایی در اکسل دارم که میخوام جمع شرطی در آن اعمال کنم با این تفاسیر که شرط مورد نظر هم در سطر اول وجود دارد و هم در ستون اول یعنی اینکه جدول به صورت سطر وستون به تعداد زیادی دارای اعداد می باشد و بنده میخوام این اعداد را جمع کنم اما مشکل اینجاست که این حمع باید با یک شرط از سطر وهمازمان با یک شرط دوم از ستون انجام شود .تمام داده ها عددی هستن – در واقع میخوام عدد متناظر بذاره یه شرط از سطر اول بگیره و یه شرط از ستون اول و درست عددی که هر دو ظرط رو داشتن از دیتا بیاره – چکنم – اگه میتونین راهنمایی کنین و پاسخ رو ایمیل کنین خیلی ضروریه
ممنون میشم کمکم کنید
درود
دو مدل سوال مطرح کردید
اینکه جمع بکنه داده ها رو که باید فرمول آرایه ای بنویسید
=SUM((D24:H24=”ل”)*(C25:C31=”ب”)*(D25:H31))
یا اینکه داده ای که در هر دو شرط برقراره فقط فراخوانی بشه
اگر حالت دوم هست میتونید با index, match ان کار رو بکنید
با سلام
جدول دیتایی در اکسل دارم که میخوام جمع شرطی در آن اعمال کنم با این تفاسیر که شرط مورد نظر هم در سطر اول وجود دارد و هم در ستون اول یعنی اینکه جدول به صورت سطر وستون به تعداد زیادی دارای اعداد می باشد و بنده میخوام این اعداد را جمع کنم اما مشکل اینجاست که این حمع باید با یک شرط از سطر وهمازمان با یک شرط دوم از ستون انجام شود .
ممنون میشم کمکم کنید
بازم تشکر
درود بر شما
این با sumif نمیشه
بسته به شرایط و نوع شرط ها (عددی متنی) میشه روش های مختلف فرمول نویسی استفاده کرد
سلام و ممنون از توضیحات خوب شما.
من باید مجموع مقدار فروش برای تعداد زیادی کد شناسه رو محاسبه کنم که یعنی اول باید کدها یوتیک بشن و بعد جمع فروش هر کد محاسبه و ثبت بشه. چکار باید کنم؟
درود بر شما
اول لیست یونیک رو تهیه کنید
مثلا با remove duplicate
این مقاله رو بخونید
https://excelpedia.net/remove-duplicate/
بعد هم از sumif استفاده کنید
سلام خسته نباشین من یک تابع دارم نمیتونم مشکلم رو حل کنم لطفا راهنماییم کنید (sumif(a2:10,”jib”,d2:d10
زمانی که یک سطر اضافه میکنم مثلا میخوام d2:d11 a2:a11 رو محاسبه کنه، فرمولم به همین شکل پایین باقی میمونه و مقادیر سطر جدید رو محاسبه نمیکنه یعنی فرمولم آپدیت نمیشه
(sumif(a2:a10,”jib”,d2:d10
درود
یا محدوده اضافه شده باید بین سطرها باشه (نه اخرین سطر)
یا اینکه محدوده ها رو table کنید. این مقاله رو هم بخونید
سپاس از محبتتون
با سلام
من فرمول زیر را برای یک مجموعه داده می خواهم برای ستونهای مختلفی مانند G و ….. انجام دهم . در واقع شرط اول مربوط به سال و شرط دوم مربوط به روزهای هر ماه هست. حالا می خواهم مجموع بارندگی روزهای هر ماه، به صورت ماهانه را برای هر سال جمع نماییم. به عبارت دیگر یک ستون مثلا برای سال ۱۹۸۲ که در ردیف اول جمع روزهای ماه اول و در ردیف های بعد جمع روزهای ماههای بعدی قرار بگیرند. و همینطور برای سال بعد در ستون دوم این عملیات انجام شود.
حالا چگونه می توانم وقتی فرمول زیر را در سلول اول ستون مربوط به سال ۱۹۸۲ اجرا می کنم برای سلول پایینی هم ان را ادامه بدهم که فقط شرط دو که اینجا عدد ۱ هست بشود و ۲، ۳ و …..
یا در ستون دوم که برای سال ۱۹۸۳ خواهد شد فقط شرط اول از ۱۹۸۲ بشو ۱۹۸۳ و …..
SUMIFS(G2:G13149,A2:A13149,1982,B2:B13149,1)
بسیار ممنون میشوم راهنمایی بفرمایید.
درود
اگر درست برداشت کرده باشم، سوال اینه که چطور با درگ کردن عدد ۱ بشه ۲ و …
برای اینطور مساول از توابع row() و column() بسته به جهت درگ کردن باید استفاده کنید
مثلا بجای عدد ۱، بذارید row(A1) این یعنی عدد ۱ و وقتی درگ میکنید پایین میشه ۲ و ….
برای ۱۹۸۲ هم بنویسید: ۱۹۸۱+Row(A1)
سلام
خدا قوت و با تشکر از آموزش هاتون در زمینه اکسل.
یک سوال
فرض:میخواهیم در سلول H۲ جمع اعداد داخل سلولهای A2D2F2 را بنویسد به شرط اینکه B2E2G2 برابر( الف )باشد.
و اگر B2E2G2 برابر (ب) بود در i2 جمع آنها را بنویسد.
در واقع خانه های که در این ردیف متنشان الف است را شناسایی و عدد کنار آن را در یک سلول جمع کند و خانه هایی که ب هستند را در خانه ای دیگر جمع کند.
این فرمول رو در H2 بنویسید:
همین فرمول رو برای شرط “ب” در سلول I2 هم بنویسید
این مقاله رو هم بخونید
https://excelpedia.net/and-or-function/
با سلام
لطفا راهنمایی بفرمایید
بنده دو تا شیت دارم در اولی ستون یک نام محصول ود رستون دیگر قیمت آن در شیت دیگر چندین فرد که هرکدام از روز اول تا آخر ماه یکسری خرید انجام میدهند و من میخواهم جمع خریدهایی راببینم که در شیت ۱ هستند و فرمولی بنویسم که در کنا راسم فرد جکع کل خرید گنجانده شود لطفا راهنمایی بفرمایید
ممنون
درود بر شما
از همین sumif باید استفاده کنید
توضیحات در مقاله بالا هست
سلام جواب نمی دهد مجبور هستم برای تک تک اونها vlookupبزنیم
پس سوال رو درست مطرح نکردید. فرمودید جمع فروش برای هر فرد. این یعنیsumif
vlookup جمع انجام نمیده
جستجو میکنه
سلام
یه فایل صورتحساب بانکی به فرمت اکسل گرفتم میخام جمع خرید هامو بهم نشون بده از فرمول که استفاده میکنم نتیجه صفر میشه مشکل چیه ؟؟؟؟
سلام
احتمالا اعدادی که در فایل هست به صورت متن هستند. که اگر اینطور باشه باید یک مثلث سبز رنگ کنار این سلول ها نشون میده. برای درست کردنش این سلول ها رو انتخاب کنید و گزینه Convert to Number رو بزنید.
سلام وقت بخیر
من میخوام صورتحساب بانک را در اکسل روزانه بر اساس تاریخ جمع بزنم چه فرمولی باید بنویسم
با تشکر نورزاده
درود بر شما
بسته به اینکه چه ساختاری داره و تاریخ چه جنسی هست و ….
باید از Sumif استفاده کنید
با سلام و عرض ادب
می خواهیم در یک سطر، محتویات سلولهای بین یک سلول تا سلول دیگری در همان سطر، را با هم جمع کنیم. اما سلول های شروع و پایان متغیر و بصورت تابع باشند. چگونه می توان اینکار را انجام داد؟
از ترکیب توابع Sum و Address و Match استفاده کردم اما نتیجه ای حاصل نشد:
از راهنمایی که می فرمایید صمیمانه سپاسگزارم.
درود بر شما
به اینصورت باید تغییر بدید:
بسیار عالی بود.
ممنون و سپاسگزارم.
خیلی ممنون
من یک شرط خاص دارم میخوام ببینم میشه با این تابع نوشتش یا نه
من ۳ ستون دارم که توی ستون سوم دو حالت داره که یا یک است یا صفر و این شرط به این صورت است که اگر مقدار ستون سوم ۱ بود مقدار ستون اول و ستون دوم باهم جمع شود اما اگر ستون سوم ۰ بود فقط مقدار ستون اول رو قرار بده و ستون دوم جمع نشود.
درود بر شما
شما باید از if استفاده کنید
مقاله زیر رو مطالعه کنید:
https://excelpedia.net/if-function/
با سلام و احترام
من چندتا if تو در تو نوشته ام و در آخر یه شرط میخوام بنویسم که اگر اون اتفاق افتاد همه ی شرط های قبلی خنثی و عدد صفر نمایش داده شود
ممنون میشم اگر کمک بفرمایید.
درود بر شما
یک if دیگه اضافه کنید و همه اون if ها رو در قسمت value false بنویسید
در قسمت value true هم صفر
بسیار سپاسگزار و ممنونم
سلام
مطالبی که در سایت گذاشتید برای من خیلی مفید بود.
ممنون وهمیشه موفق باشید
درود بر شما
زنده باشید، لطف دارید
موفق باشید
جناب آقای چراغی
سلام علیکم
در نوشتن یک تابع سئوال داشتم .
چنانچه امکان دارد کمکم کنید.لطفا
طرح مسئله :
در شیت ۱ : اطلاعات در یک جدول موجود است که شامل ستونهای (ردیف ، تاریخ ، نام ) می باشد.
می خواهم در جدول دیگر با درج یکی از ردیف های موجود در جدول اول ، نام نیز فراخوانی شود.
توابع زیادی مثل , index,find,mach,sumif,sumifs,lookup,vlookup,hlookup را امتحان کردم ولی موفق نشدم.
فایل مربوطه را برایتان به آدرس ایمیل ارسال نمودم . لطفا در صورت امکان تابع مورد نظر را در فایل اکسل نوشته و برایم ایمیل نمائید.
قبلا از شما سپاسگزاری می کنم. احمدی
سلام
از تابع VLookup استفاده کنید ولی آرگومان سوم تابع باید عدد ۵ باشه چون چند ستون پنهان شده تو جدول وجود داره.
جناب آقای سامان چراغی
با سپاس فراوان
با راهنمایی شما مشکل حل شد.
شاد و موفق باشید.
جناب آقای چراغی
سلام علیکم
در نوشتن یک تابع سئوال داشتم .
چنانچه امکان دارد کمکم کنید.لطفا
طرح مسئله :
در شیت ۱ : اطلاعات در یک جدول موجود است که شامل ستونهای (ردیف ، شماره سند ، تاریخ ، کد حساب ، مبلغ ) می باشد.
می خواهم مجموع اعداد ستون مبلغ مندرج در جدول را با توجه به کد مورد نظر در سلولی نشان دهد. که با فرمول SUMIFS توانستم.
می خواهم مجموع اعداد ستون مبلغ مندرج در جدول را در یک بازه زمانی در سلولی نشان دهم . که با فرمول SUMPRODUCT توانستم.
مشکل اینجاست که نمی توانم دو فرمول را باهم ترکیب کنم. یعنی مجموع فروش کد حساب مورد نظر در بازه زمانی مشخص.
فایل مربوطه را برایتان به آدرس ایمیل ارسال نمودم . لطفا در صورت امکان تابع مورد نظر را در فایل اکسل نوشته و برایم ایمیل نمائید.
قبلا از شما سپاسگزاری می کنم. احمدی
سلام جناب احمدی
کافیه در فرمولی که با Sumproduct نوشتید شرط کد رو اضافه کنید، با توجه به فایلتون نتیجه فرمول به صورت زیر میشه:
جناب آقای سامان چراغی
با سلام و دورد
نمی دانم چگونه مراتب تشکر خودم را ابراز نمایم.
انشاء ا.. همراه صحیح ، سالم ، موفق و شاد باشی.
احمدی
سئوال دیگری نیز داشتم چنانچه زحمتی نیست آن را هم راهنمایی فرمائید.
سلام
چطورمیتونم با sumif جمع خانه های رنگی رو حساب کنم ؟
این فرمول رو نوشتم جواب نداد SUMIF(B4:AE4؛ “white”)
سلام
شرایطی که تو تابع Sumif قابل استفاده هست به محتوای سلول برمیگرده نه رنگ و یا سایر خصوصیات سلول.
برای انجام جمع شرطی بر اساس رنگ سلول میتونید از تابعی که تو فایل زیر گذاشته شده استفاده کنید:
دانلود فایل