نحوه استفاده از تابع SUMIFS در اکسل

تابع sumifs

توابع اکسل  یکی از مهمترین بخش های این نرم افزار هستند که شناخت و استفاده مناسب از اونها میتونه به ما در سرعت بخشیدن به تحلیل اطلاعات کمک زیادی بکنه.  در این مطلب میخوایم با نحوه استفاده از تابع SUMIFS در اکسل آشنا بشیم

نکته: برای اطلاع از نحوه استفاده از تابع SUMIF به این مطلب مراجعه کنید.

تابع SUMIFS برای جمع مقادیر یک محدوده که دارای معیارهای خاصی هستند استفاده میشه. این معیارها میتونن در محدوده های دیگه ای به جز محدود ای که قصد داریم مقادیر اون رو جمع کنیم قرار داشته باشن. قاعده کلی تابع SUMIFS به شکل زیره:

(…, شرط جمع2 ,محدوده معیار2 ,شرط جمع1 ,محدوده معیار1 ,محدوده مقادیر موردنظر برای جمع زدن)SUMIFS

فرض کنید جدولی به شکل زیر داشته باشیم که در اون ستون اول روزی از ماه باشه که سفارش فروش برای محصول خاصی ثبت شده. ستون دوم نام محصولی باشه که براش سفارش فروش ثبت شده و ستون سوم هم مقدار سفارش از محصول مورد نظر رو نشون بده.

تابع sumifs

حالا اگه بخوایم مقدار سفارش رو برای یک محصول در روز مشخصی از ماه بدست بیاریم میتونیم از تابع SUMIFS استفاده کنیم. فرض کنید میخوایم جمع مقدار سفارش هایی رو که برای محصول ماوس در روز اول ماه ثبت شده محاسبه کنیم. چون میخوایم جمع مقادیر سفارش رو محاسبه کنیم پس محدود مقادیر مورد نطر برای جمع زدن، در این مثال محدوده  C2:C8  هست. اولین معیار ما برای جمع زدن اینه که سفارش مربوط به محصول ماوس باشه. پس محدوده معیار1 ما برابر  B2:B8  هست و چون باید محصول موردنظر ماوس باشه شرط جمع1  “ماوس=”  میشه. معیار دوم ما برای جمع مقادیر سفارش این بود که سفارش مربوط به روز اول ماه باشه. این هم دقیقا مثل معیار قبلی هست و محدوده معیار2 ما برابر  A2:A8  و شرط جمع2  “1=”  میشه. خب پس فرمول ما به شکل زیر کامل میشه:

( “1=” ;A2:A8;”ماوس=” ;SUMIFS(C2:C8; B2:B8

حالا با قرار دادن فرمول بالا در سلول C9، جدول ما به شکل زیر تکمیل میشه و جمع مقدار سفارشات مربوط به محصول ماوس در روز اول ماه که برابر عدد 11 هست محاسبه میشه.

تابع sumifs

 

دانلود فایل اکسل تابع SUMIFS

 

 

84 Comments on “نحوه استفاده از تابع SUMIFS در اکسل”

    1. یاسین اسماعیل پور

      ممنون از لطفت. انشالله بعد از عید که قراردادم با محل کارم تموم میشه فرصت بیشتری دارم و قول میدم که مطالب بیشتری رو اینجا قرار بدم.

  1. amir

    با سلام
    ممنون از سایت خوب شما
    سوالی که داشتم این است :
    مثلا در جدول مثال بالا می خواهیم جمع تجمع تا روز چهارم ر در یک سلول و روز چهارم در سلول دیگر نشان بدهد.

    ممنون

  2. vahidasadi

    متشکرم از فایل نمونه ای که قرار دادید.خیلی زود مشکلم حل شد.خیلی عالی بود.

  3. ali

    با سلام
    من یک فایل اکسل دارم با مشخصات زیر
    شیت اول کل فروش فروشندگان از همه اقلام
    کد فروشنده / نام فروشنده/ کد کالا/ نام کالا / تعداد
    و شیت بعدی 50 قلم منتخب را داریم که میخواهم محاسبه کنیم چقدر فروختند
    کد کالا / نام کالا / تعداد فروخته شده از این کالا /
    به چه شکل میشه ((تعداد فروخته شده از این کالا)) را حساب کرد

    1. یاسین اسماعیل پور

      سلام علی جان
      میتونی برای اینکار از همین تابع sumifs استفاده کنی

  4. ali

    با تشکر از شما بابت سایت خوب و مفیدتون – سوالم را در پست بالا پرسیدم و با sumif به جواب رسیدم والان می خواهم میزان فروش هرفروشنده را بدست بیاورم اینو به چه شکل مییشه محاسبه کرد

    1. یاسین اسماعیل پور

      شما در تابع sumifs به هر تعدادی که بخوای میتونی شرط اضافه کنی علی جان

  5. مهدی

    سلام.
    توی فرمول sumifs یه شرط دارم که بازه ی زمانی داره واین بازه ی زمانی رو نمیخوام داخل فرمول به صورت دستی تایپ کنم.میخوام با انتخاب سلول های مد نظرم این فرمول تکمیل بشه.
    به عبارت دیگه میخوام ارزش یا مقدار داخل سلول رو که زمان ه بخونه و داخل فرمول استفاده کنه

    1. یاسین اسماعیل پور

      سلام. فرض کن تاریخت در سلول A1 قرار داره. در این حالت شرط شما به جای مثلاً “<1394/01/06" میشه “< "&A1

  6. سیروس

    سلام
    ایا میتونم بطور خودکار جمع یک ستون را در ستون کناری واردکنم(2برابر)
    Bتعداد ستونها زیاد است 5هزار تا و میخواهم در ستون کناری دوبرابر هر ستون در ستون کناری بیاید.
    ایا میتوان شرط گذاشت که عمل فوق فقط برای اعداد بالاتر از مثلا 25 انجام شود و از بقیه صرف نظر کند؟
    باتشکر فراوان
    X 2X
    Y 2Y
    Z 2Z
    D 2D
    B 2

    1. یاسین اسماعیل پور

      بله میشه سیروس جان. این فرمول رو در سلول B1 بنویس و تا پایین کپی کن:
      =IF(a1>25;a1*2;0)

  7. پوریا

    سلام دوست عزیز
    تشکر از سایت بسیار مفیدتون
    من یه سوال داشتم
    اگه بخوایم روز سفارشون هم 1 باشه هم 2 اون موقع فرمول به چه شکلی میشه
    ممنون.

    1. یاسین اسماعیل پور

      سلام پوریا جان
      برای اینکار شما باید دو تا sumifs بنویسی و انها رو با هم جمع کنی

  8. حسن طالبی

    با عرض سلام و خسته نباشید
    در صورت امکان فرمول محاسبه عیدی برا ی کارکنان را که عبارت زیر است را در اکسل نشان دهید :
    عیدی برابر است با دو برابر حقوق پایه به شرطی که از 3 برابر حداقل حقوق بیشتر نباشد ، اگر بیشتر شد همان سه برابر حداقل حقوق ملاک باشد .

    1. یاسین اسماعیل پور

      سلام حسن جان
      فرض کنید حقوق پایه در سلول a1 و حداقل حقوق در سول b1 هست. فرمول میشه :
      =if(a1*2>b1*3;b1*3;a1*2)

  9. بهمن

    سلام . من میخوام همین فرمول ستون BوCرادر چندین شیت جستجو کنم . نمیشه . لطفا راهنمایی کنید
    بهمن
    مثلا در شیت۱:ماوس ۲عدد
    شیت۲:ماوس ۳عدد
    میخوام تعداد ماوس ها را در همه شیت ها جمع ببندم

  10. مهدی

    سلام منم مثل بقیه از سایت خوبتون تشکر میکنم و این ک تازه دارم رو اکسل کار میکنم و میخوام جمع چن نوع محصول و بزنه تعدادشون بیش از 10 تا است و این ک نمیخوام از جمع SUFIFS استفاده کنم….
    راهی هست ک بشه چک کنه از روی یه لیست دیگه ک اونارو قبلا وارد کردم و بعد جمع بزنه…
    ببخشید اگه بد توضیح دادم چون تازه کارم اگه بتونین راهنماییم کنین ممنون میشم.

    1. یاسین اسماعیل پور

      فرض کن لیست تو e1:e2 باشه. در اینصورت ا فرمول زیر میتونی استفاده کنی:
      =SUMPRODUCT(SUMIFS(B1:B6;A1:A6;E1:E2))

  11. یاس ابی

    سلام سایتتون عالیه . من یه سوال داشتم که از هر کی پرسیدم نتونسن بهم جواب بده . لطفا اگه میتونید راهنماییم کنید .
    اگه یه جدول اصلی داشته باشیم به طور مثال با 30 تا شرح که تو هر کدوم از اینا یه مبلغی باشه و چند تا جدول فرعی که هرکدوم یه سری از شرح حای جدول اصلیو داشته باشن چه جوری میتونم اطلاعات اون جدولای فرعی طوری کپی کنم داخل جدول اصلی که خود اکسل شرح درست تشخیص بده . مثلا جدول اصلی علی ، حسن ، حسین، و غیره داره جدول فرعی امیر و حسن . می خوام اطلاعات حسن وقتی از جدول فرعی به اصلی کپی پیست میکنم تو محل خود حسن قرار بگیره . ببخشید که طولانی شد .

    1. یاسین اسماعیل پور

      دوست عزیز لطفا فایلتون رو برام ارسال کنید تا بهتر ذبتونم راهنماییتون کنم

  12. تقی زاده

    سلام وقت به خیر. من یه جدول اکسل دارم می خوام بهش شرط بدم در ستون مورد نظر که اعداد از صفر داره تا 100 میلیون. می خوام فرمول بدم اعداد بین بیشتر از 5میلیون و کمتر یا مساوی 20 میلیون ریال رو جمع بزنه . لطفا منو راهنمایی کنید.
    با تشکر

  13. arashk

    جناب آقای اسماعیل پور
    با عرض ادب و احترام
    ابتدا تشکر می کنم از شما بابت سایت بسیار مفید و ارزشمندتون
    بنده به تازگی به جمع دوستان ملحق شدم و از این بابت بسیار خرسند هستم
    در خصوص جدول فوق،بنده سوالی داشتم خدمت شما
    اگر بخواهیم بدون در نظر گرفتن ستون A که روز سفارش می باشد.فقط محصولات مشابه را دراین جدول جستجو کند و جمع هر کدام را در سلول روبروی یک محصول ثبت کند آیا با همین تابع قابل اجراست؟
    بعنوان مثال:
    تعداد کل ماوس ها را در سطر 2 و ستون D ثبت کند و تعداد کل کیبوردها را در سطر3 و ستونD ثبت کند
    با تشکر

  14. toraj85

    با سپاس از شما آقای اسماعیل پور
    لطفاً بفرمائید مفهوم = در کنار “ماوس” و یا در کنار “1” چیست؟ چون با حذف = جواب بازهم درست خواهد بود.

  15. سید حسین

    با سلام و خسته نباشید.اگه بخواهیم با تابع sumif مجموع موجودی چند کد کالا را حساب کنه چیکار باید کرد؟یعنی به جای یک شرط چند شرط داشته باشیم.

    1. یاسین اسماعیل پور

      سلام حسین جان
      فرض کن میخوای سلول هایی که یک یا دو هستند رو جمع کنی. برای اینکار میتونی از فرمول زیر استفاده کنی:
      =SUM(SUMIF(A4:A100;{1;2};B4:B100))

  16. وبلاگ من را نیز با قدوم خودت مزین کن!!

    سلام ، یاسین جان
    خیلی خوشحال شدم که بعد از مدتها ، مشغله های کاریت اجازه داد که سری به سایت خودت بزنی و بنده مفتخر باشم که روی گلت را در اینجا ببینم . از همت بلندت در راه اندازی این سایت و دیگر سایتهات ، صمیمانه تشکر میکنم . من که به نوبه خودم ، بدجوری به سرزدن به این سایت عادت کردم و از تبادل اطلاعات با دیگر دوستان ، فوق العاده بهره مند میشم و لذت می برم ، امیدوارم در تمامی عرصه ها و ابعاد زندگیت موفق باشی
    باتشکر – خانیکی
    0919-302-6452

    1. یاسین اسماعیل پور

      سلام دوست عزیز
      بله بعد از مدتها فرصتی فراهم شد انشالله که بتونم از این به بعد بیشتر وقت بگذارم. حضور شما در این سایت باعث افتخار هست جناب خانیکی عزیز. حتما به وبلاگ شما هم سر خواهم زد.
      شاد و سربلند باشید

  17. داود یوردخانی

    با سلام
    چجوری میشه با تایع sumifs در یک لیست کالا مثلا نام کالاها به این صورت می باشد که ( کتان چاپی سرمه ای ) سلول هایی را با هم جمع کرد که فقط عبارت کتان داخلشون هست بدون توجه به رنگ یا نوعش ؟
    من میخواهم مثلا مقدار فروش کتان را از این لیست بدست بیاورم ولی چون همراه کتان نوع و رنگش هم در عبارت آمده است در همان سلول نمی توانم .
    لطفا راهنمایی کنید با تشکر

  18. فرید

    سلام
    تعدادی داده در ستون a دارم.
    میخواهم سه تا سه تا در ستون b جمع بزند
    چکار کنم؟
    مثلا :
    جمع a1 تا a3 در سلول b1
    جمع a4 تا a6 در سلو b2
    جمع a7 تا a9 در سلول b3
    الی اخر…

    1. انجام پروژه های اکسل در اینجا

      سلام ، آقا فرید – بهتر بود اعداد ستون A بجای یک ستون در سه ستون نوشته و سپس به راحتی باهم جمع میشد ، اما با وجود این ، راه سادش اینه که با یک فرمول جمع ساده ، جمع a1 تا a3 را در سلول b1 بنویسی و همین فرمول رو در خانه b4 و b7 و تا آخر کپی کنی ، اگه این راه حل برات کافی نیست ، مشکلشو بگو ، تا راه حل دیگه ای بهت بگم
      موفق باشی – خانیکی
      0919-302-6452

  19. جعفری

    با سلام
    میخواستم تابع minرو برای یک ردیف بنویسم که صفر رو جزء کوچیکترین عدد حساب نکنه یعنی عدد بعد از صفر رو بعنوان کوچکترین عدد نمایش بده.
    با تشکر از راهنماییتون

    1. یاسین اسماعیل پور

      سلام دوست عزیز
      برای اینکار فرمول زیر رو بنویسید و بعد از کلید ترکیبی CTRL+SHIFT+ENTER استفاده کنید. به جای A:A محدوده مدنظرتون رو قزار بدید:
      =MIN(IF(A:A>0;A:A))

  20. غفوری

    سلام دوست عزیز
    از سایت خوبتون واقعا ممنونم
    یه مشكلی دارم و ممنون میشم اگه راهنمایی بفرمایید
    در استفاده از تابع sumifs میشه یكی از شرطها، مقدار بیشتر از یك عدد معین باشه، مثلا مقادیر بیشتر یك میلیون، اما اگه این مقدار، داخل یك سلول نوشته بشه و شرط بزاریم كه اعداد بیشتر از مقدار این سلول جمع زده بشه، جواب نمیده، یعنی عملگرهای مقایسه‌ای، رد صورتی كه مقدار یك سلول بهشون داده بشه، در جمع شرطی جواب نمیدن. لطفا راهنمایی بفرمایید كه چه كار باید انجام بدم

    1. یاسین اسماعیل پور

      چرا جواب نده جناب غفوری؟ شما باید شرطتون رو به اینصورت بنویسید:
      “>=”&A1
      نباید A1 رو هم در “” بیارید

  21. omid

    آقا من یک فایل اکسل دارم
    a1=هزینه
    a2=سهم نفر اول
    a3=سهم نفر دوم
    a4=سهم نفر سوم
    a5=سهم نفر چهارم
    هزینه تقسیم بر چهار میشه و a2:a5 هر کدام یک چهارم a1( هزینه) است
    حالا میخوام بدونم چیکار کنم اگر هزینه مربوط به دو نفر نمیشد(مثلا a3 و a5) مبلغ هزینه a1 بطور مساوی بین سایر افراد تقسیم بشه

  22. شیلا

    سلام خسته نباشید میشه در یک فرمول راهنمایی کنید
    در ستون بی تا سطر 6 اعداد دارم ک متغیر هستش و ممکن برخی سلولها خالی باشن ستون سی رو میخوام با توجه ب پر و خالی بودن ستون بی تیک یا عدم تیک کنم ودر سلول شماره 7 فرمولی بنویسم اگه سلولی در ستون سی تیک داشت با سلولی بی جمع بسته بشه مثلن b1 قیمت نوشتم در c1 تیک میزنم b2 قیمت نیست در c2 تیک نمیزنم و b3 قیمت در c3 تیک میزنم حالا توی c4 میخوام باتوجه به این سلولا جمعشون زده بشه.
    محبت میکنید ک راهنماییم کنید .

    1. یاسین اسماعیل پور

      سلام شیلا جان. اصلا لازم به تیک زدن ستون C نیست. شما شرط جمعت رو میزاری تاینکه خالی نباشه سلول < <>>””. همین

  23. حفاری

    سلام میتونم یه سوال بکنم در مورد sumifs
    من یه جدول اکسل دارم با چند شیت متفاوت بابت ماههای کارکرد متفاوت ، در ستون اول هر شیت نام شرکت ، در ستون دوم تعداد گواهی های صادر شده و در ستون سوم مبلغ پرداختی بابت هر گواهی
    میخوام به تفکیک هر شرکت تعداد گواهی ها و مبالغ پرداختی را محاسبه کنم
    شرکت ها در شیت های مختلف عموما هم نام نیستند ، چطور انجام میشود .

  24. سپیده

    با سلام و سپاس از سایت بسیار خوبی که دارید.
    سوالی داشتم در زمینه مثال بالا!
    فرض کنید شرط نوع محصول و روز سفارش متغیر باشند و بخواهیم به طور هم زمان از تعداد فروش محصلات مختلف در روزهای مختلف مطلع شویم، یعنی به این صورت که در سلولهای جداگانه چگونه میتوانیم تعداد موس های فروخته شده در روز اول،در روز دوم و … همچنین به همین ترتیب تعداد دیگر محصولات در روزهای مختلف دیگر را به طور هم زمان محاسبه کنیم!
    با تشکر.

  25. احسان رعنائی

    سلام و عرض ادب مهندس

    سوالی که از خدمتتون داشتم اینه که چطور میشه دو شرط رو همزمان اعمال کرد
    تو مثال خودتون ماوس و کیبورد رو جمع ببنده
    اگر دستورش رو بنویسید ممنون میشم

  26. علی

    سلام ممنون از توضیحات عالیتون
    فقط یک سوال در مثالی که توضیح دادین اگر بخواهیم شرط جمع 2 را به عنوان مثال برای تمامی روزهای یک ماه در نظر بگیریم نه فقط برای یک روز خاص (از روز اول تا سی ام ماه) طرز نوشتاری در فرمول به چه صورت می شود ؟ خیلی ممنون میشم توضیح بدید

  27. محمدرضا

    دوستان خوب من پيشنهاد مي كنم از كريستال ريپورت و امكانات آن استفاده نماييد . خيلي از اين سئوالها كه مطرح شده است به راحتي در اين ابزار قابل پياده سازي هستند . خوشبختانه اين ابزار به اكسل هم متصل مي شود و گزارشهاي فوق العاده اي با امكانات زيادي در اختيار شما مي گذارد .
    درود به همه شما
    موفق باشيد

  28. احسان

    با سلام
    تو شیت 1 سه تا ستون دارم تاریخ و عنوان (مثلا اداری) و مخارج. میخواستم فرمولی بنویسم که بین تاریخ 95/09/01 تا 95/09/10 با عنوان اداری چقدر هزینه برداشته..با فرمول زیر من بجوابم میرسم اما من میخوام این تاریخ رو تو سلول ( مثال سلول A1 در شیت 2 ) بصورت دستی وارد کنم چجوری این سلول رو تو فرمول آدرس دهی کنم تا جواب بده ممنون میشم راهنمایی کنید.

    =SUMIFS(AZAR!G:G;AZAR!C:C;”=اداری”;AZAR!B:B;”>=95/09/23″;AZAR!B:B;”<=95/09/30")

  29. علی

    ممنون از شخصیت بسیار بالای شما که علم خودتون رو بدون هیچ چشم داشتی به همه آموزش می دهید. امیدوارم خدا اجرتون رو بده (انشاالله)

  30. seyed ahmad

    سلام
    ممنون بابت مطالبتون
    اگه بخام سلول هایی که یک رنگ خاص دارند رو باهم جمع کنم، باید از چه تابعی استفاده کنم
    (سلول ها رو به صورت دستی رنگ کردم واز conditional formating استفاده نکردم)

  31. امین

    سلام .
    دوست عزیز آیا امکان دارد محدوده معیار ها criterial_range1 و criterial_range2 یکی باشند اما شرط ها criterial با هم متفاوت باشند !؟

  32. محسن

    سلام
    مهندس جان قربووونتت یه سوال داشتم اگه لطف کنید و جواب بدین ممنون میشم
    آیا امکانش هست که sumifs رو در محدوده match انجام داد مثلا شما از بین کل روزهای سال که در اکسل ثبت کردین میخواین یکبار از اول فروردین تا آخر فروردین گزارش گیری کنین یکبار دیگه میخواین از مثلا 20 فروردین تا 8 اردیبهشت گزارش بگیرین
    ممنون میشم کمکم کنید

  33. ebi

    سلام يك راهنمايي مي خواستم كه چگونه مي شود براي دوستون مشابه ستونهاي A,B مثال فوق دوستوني جديد جديد ايجاد كرد كه نشان دهد ازهر محصول جمعا چه تعداد سفارش داشته ايم يعني ستونهاي جديد برابر مثال فوق دورديف ماوس وكيبورد خواهد داشت وجلوي هر كدام جمع سفارشات ممنونم

  34. سرحلقه

    سلام تشکر از راهنمائی شما و متال هائی که می زنید
    ولی من این فرمول شما را عمل کردم جواب نداد
    ( “1=” ;A2:A8;”ماوس=” ;SUMIFS(C2:C8; B2:B8
    با یک کمی کار متوجه شدم علامتهای مساوی = نباید داخل کوتیشن باشند
    با حذف تساوی نتیجه درست شد عدد 11
    ( “1” ;A2:A8;”ماوس” ;SUMIFS(C2:C8; B2:B8
    من از همینجا آموزش دیدم خوب بود بهتر است دقت بیشتری بفرمائید
    تشکر

  35. سرحلقه

    سلام آقای مهندس
    این فرمول شما را عمل کردم جواب نداد
    ( “1=” ;A2:A8;”ماوس=” ;SUMIFS(C2:C8; B2:B8
    با کمی کار متوجه شدم علامت مساوی = را نباید داخل کوتیشن گذاشت
    با حذف علامت تساوی جواب گرفتم تشکر از شما بهرحال دقت بیشتری بفرمائید
    ( “1” ;A2:A8;”ماوس” ;SUMIFS(C2:C8; B2:B8
    ممنون

  36. Ali

    سلام
    به عنوان مثال تو خونه b6 ,d6,f6 عدد دارم
    و تو خونه c6,g6 نوشته شده سفید و تو خونه g6 نوشته شده قهوه ای
    چطور با دستور sumifs خونه ی که سفید هستش رو عدداش جمع کنه؟

  37. شیدا

    باسلام
    آقا یاسین
    از این دستور هم در صورت وضعیت خود استفاده نمودم و نتیجه گرفتم، باز هم ممنونم .
    انشاالله که خدا هم در دنیا و هم در آخرت اجرتان دهد.
    باتشکر- شیدا

  38. مسعود

    سلام چطور در اکسل از بین 0 تا 100 اعداد فرد را با هم دیگر جمع کنم
    یعنی فرمول حاصل جمع اعداد فرد بین 0 تا 100 رو چطور باید بنویسم
    ممنون از پاسخگویی

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *