جمع شرطی در اکسل – آموزش تابع و فرمول به زبان ساده

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

به همین دلیل، فرمول جمع شرطی در اکسل زمانی به درد می‌خورد که نیاز به جمع بستن برخی داده‌ها طبق شروطی خاص دارید. مزیت یادگیری SUMIF این است که پس از آن، یاد گرفتن باقی زیر مجموعه‌های تابع IF در اکسل (مانند COUNTIF، AVERAGIF و غیره) بسیار ساده‌تر خواهد شد. پس اگر در یادگیری اکسل مشکل دارید، بهتر است از همین تابع شروع کنید. همچنین مطالعه مطلب آموزش اکسل رایگان از صفر و تماشای فیلم رایگان آن هم کمک‌کننده است.

تابع جمع شرطی در اکسل

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

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

1SUMIF(range, criteria, [sum_range])

این تابع، سه آرگومان دارد که دو مورد اول ضروری هستند و سومی اختیاری است.

• Range یا محدوده (ضروری): محدوده‌ای از سلول‌ها که می‌خواهید شرط خود را اعمال کنید.
• Criteria یا شرط (ضروری): شرطی که می‌خواهید اجرا کنید که می‌تواند عدد، متن، تاریخ، عبارت منطقی، مرجع سلول یا تابعی دیگر باشد. مثلا «5»، «گیلاس» یا «1401/06/21».
• Sum-range‌ یا محدوده جمع (اختیاری): محدوده‌ای که در صورت برآورده شدن شرط، جمع در آن انجام خواهد شد. در صورتی که در فرمول وارد نشود، جمع برحسب داده‌های Range انجام می‌شود.

توجه کنید که شرط های متنی یا داد‌ه‌های منطقی باید با علامت نقل قول ("") مورد استفاده قرار بگیرند. مثل "سیب"، "10<" و باقی شرط‌های این‌چنینی. همچنین ارجاعات سلولی باید بدون ("") نوشته شوند، در غیر اینصورت، SUMIF‌ آن‌ها را به‌عنوان رشته‌های متنی در نظر می‌گیرد.

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

فرمول ساده جمع شرطی در اکسل

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

• با افزودن آرگومان‌ها شروع کنید.
  • محدوده، فهرست مناطق است (B2:B10)، یعنی از سلول B2 تا سلول B10.
  • معیار یا شرط، "تهران" است یا سلولی که شهر مورد نظر را در خود دارد (F1).
  • محدوده جمع، مقدار فروش است، از سلول C2 تا C10 یا C2:C10.

با کنار هم قرار دادن آرگومان‌ها، به فرمول زیر خواهید رسید:

1=SUMIF(B2:B10, "تهران", C2:C10)

یا

1=SUMIF(B2:B10, F1, C2:C10)

هر دو فرمول فقط مقادیر فروش در شهر تهران را جمع می‌زنند. توجه کنید که آرگومان «محدوده جمع» (Sum-range)، فقط سلول سمت چپ بالای محدوده‌ای که باید جمع شود را مشخص می‌کند. بقیه سلول‌ها به‌کمک آرگومان «محدوده» (Range) پیدا می‌شوند. در این مثال، ستون مربوط به مقدار فروش، Sum-range و ستون شهرها، Range است.

این یعنی می‌توانید ستون‌ها و ردیف‌های متفاوتی برای آرگومان Sum-range تعریف کنید اما سلول بالا سمت چپ، همیشه باید درست باشد. به‌عنوان مثال، می‌توانید در فرمول بالا، C2 ،C2:C4 یا حتی C2:C100 را جایگزین کنید و در نهایت، نتیجه درست خواهد بود. با این تفاصیل، بهترین روش این است که Sum-range و Range اندازه یکسانی داشته باشند.

مثال‌هایی از استفاده فرمول SUMIF در اکسل

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

SUMIF‌ برای شرایط بزرگ‌تر و کوچک‌تر

برای جمع اعدادی بزرگ‌تر یا کوچک‌تر از مقداری خاص، باید معیار SUMIF را با یکی از عملگرهای منطقی زیر به‌کار ببرید:

• بزرگ‌تر از (<)
• بزرگ‌تر مساوی (=<)
• کمتر از (>)
• کمتر مساوی (=>)

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

1=SUMIF(C2:C10, ">3", B2:B10)
2

اگر عدد مورد نظر در سلول دیگری قرار دارد (در مثال ما در سلول F1)، ‌می‌شود فرمول را به‌شکل زیر هم نوشت:

1=SUMIF(C2:C10, ">"&F1, B2:B10)

در برخی شرایط ممکن است نیاز داشته باشید اعداد کمتر از مقدار خاصی را با هم جمع کنید. در این حالت باید از عملگر «کمتر از» (>) استفاده کنید.

1=SUMIF(C2:C10, "<3", B2:B10)
2

فرمول SUMIF با شرط برابری

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

1=SUMIF(C2:C10, 3, B2:B10)
2

یا

1=SUMIF(C2:C10, "=3", B2:B10)
2

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

1=SUMIF(C2:C10, F1, B2:B10)
2

در این مثال، B2:B10 مقادیر موردنظر هستند، C2:C10 مدت زمان حمل و نقل و F1 زمان تحویلی است که در نظر دارید.

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

1=SUMIF(A2:A10, "جاروبرقی", B2:B10)

1=SUMIF(A2:A10, "=جاروبرقی", B2:B10)

1=SUMIF(A2:A10, F1, B2:B10)

در این جا، A2:A10 فهرست اقلامی است که بر حسب مقدار موجود در سلول F1 مقایسه می‌شوند.

در فرمول‌هایی که دیدیم، شرط به‌طور کامل با محتوای سلول موردنظر تطابق داشت. مثلا برای جمع "جاروبرقی" فقط "جاروبرقی" ها جمع می‌شوند و به‌عنوان مثال، جمع "جاروبرقی‌ها" حساب نخواهد شد. برای جمع کردن تطبیق‌های جزئی، شرط دیگری را استفاده می‌کنیم که در ادامه همین مقاله خواهید دید.

همچنین توجه داشته باشید که در فرمول‌های SUMIF اکسل عملگرهای مقایسه‌ای یا عملگر تساوی همیشه در ("") قرار می‌گیرند. چه به تنهایی استفاده شده باشند و چه همراه با عدد یا متن دیگری نوشته شوند.

• مطالب پیشنهادی برای مطالعه:
  • کاربرد اکسل چیست؟ – ۲۱ استفاده مهم که باید بدانید
  • چگونه اکسل یاد بگیریم؟ – راهنمای یادگیری آسان و سریع
  • جمع ستون در اکسل — ۵ روش کاربردی به زبان ساده + فیلم آموزشی رایگان
  • نوشتن فرمول جمع در اکسل — به زبان ساده + مثال و فیلم آموزشی رایگان

فرمول SUMIF‌ با شرط عدم برابری

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

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

1=SUMIF(C2:C10, "<>3", B2:B10)

یا مثلا برای پیدا کردن جمع تمام موارد به‌غیر از «جاروبرقی»، باید فرمول SUMIF‌ زیر را در اکسل بنویسید:

1=SUMIF(A2:A10, "<>جاروبرقی", B2:B10)

اگر مقداری که می‌خواهید متضادش را پیدا کنید در یک سلول بود، ترکیب عبارت عدم تساوی و سلول در SUMIF‌ به شکل زیر می‌شود:

1=SUMIF(A2:A10, "<>"&F2, B2:B10)

فرمول SUMIF برای سلول‌های بدون مقدار

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

اگر منظور از «خالی» این باشد که مطلقا هیچ چیز درون سلول نیست (نه فرمول، نه رشته متنی و نه هیچ چیز دیگر)، در این شرایط از "=" به‌عنوان معیار استفاده کنید:

1=SUMIF(B2:B10, "=", C2:D10)

اگر سلول حاوی رشته‌های بي‌معنا (به‌عنوان مثال فرمول خالی ""=) باشد، فرمول با استفاده از ("") به‌عنوان شرط، نوشته می‌شود:

1=SUMIF(B2:B10, "", C2:D10)
2

هر دوی این ‌فرمول‌ها در نهایت مجموع فروش را برای شهرهای مشخص نشده، به دست می‌آورند. یعنی جایی که سلولی در «ستون 2» خالی است.

فرمول SUMIF برای سلول‌های حاوی مقدار

در ادامه فرمولی را داریم که برای جمع سلول‌هایی استفاده می‌شود که سلول متناظر آن‌ها در ستون دیگر، خالی نیست. برای این کار از عملگر «<>» استفاده می‌شود. با این کار تمام سلول‌هایی که محتوایی دارند، با هم جمع می‌شوند (و این قاعده، سلول‌هایی که حاوی رشته‌هایی با طول 0 هستند را هم شامل خواهد شد).

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

1=SUMIF(B2:B10, "<>", C2:D10)

فرمول SUMIF در اکسل با شرط متنی

زمانی که اعداد یک ستون را بر حسب متنی در ستون متناظر با هم جمع می‌کنیم، نیاز است که حتما بین تطبیق دقیق و تطبیق جزئی شرط، تفاوت قائل شویم. در ادامه مثال‌هایی از تطبیق جزئی و تطبیق کامل را در شرایط مختلف می‌بینید:

• شرط: جمع کردن در شرایط برابری.
  • فرمول تطبیق کامل: =SUMIF(A2:A8, "جاروبرقی", C2:C8)
  • توضیحات: این یعنی جمع مقادیری در سلول‌های C2:C8، به شرطی که یک سلول متناظر در ستون محصولات، دقیقا کلمه "جاروبرقی" را در خود داشته باشد. سلول‌هایی حاوی عبارات "جاروبرقی قرمز"، "کیسه جاروبرقی" و "جاروبرقی‌ها" جمع نمی‌شوند.
• شرط: جمع سلول‌های حاوی مقدار.
  • فرمول تطبیق جزئی: =SUMIF(A2:A8, "*جاروبرقی*", C2:C8)
  • توضیحات: این یعنی جمع مقادیر در سلول‌های C2:C8، اگر یک سلول متناظر در ستون محصولات شامل عبارت "جاروبرقی" باشد، چه به‌تنهایی و چه در ترکیب با کلمات دیگر. سلول‌های شامل عبارات "جاروبرقی قرمز"، "کیسه جاروبرقی" و "جاروبرقی‌ها"، جمع می‌شوند.
• شرط: جمع در شرایط عدم برابری.
  • فرمول تطبیق کامل: =SUMIF(A2:A8, "<>جاروبرقی", C2:C8)
  • توضیحات: این یعنی جمع مقادیر سلول‌های C2:C8، اگر سلول متناظر در ستون محصولات شامل هر عبارتی به‌غیر از "جاروبرقی" باشد. سلول‌های حاوی ترکیب این عبارت با کلمات دیگر، مثل "جاروبرقی قرمز"، "کیسه جاروبرقی" و "جاروبرقی‌ها"، جمع می‌شوند.
• شرط: جمع سلول‌های بدون مقدار.
  • فرمول تطبیق جزئی: =SUMIF(A2:A8, "<>*جاروبرقی*", C2:C8)
  • توضیحات: این یعنی جمع مقادیر سلول‌های C2:C8، اگر سلولی در ستون محصولات شامل عبارت "جاروبرقی" نباشد. سلول‌های حاوی عبارت‌های ترکیبی مانند "جاروبرقی قرمز"، "کیسه جاروبرقی" و "جاروبرقی‌ها"، جمع نمی‌شوند.

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

فرمول جمع شرطی در اکسل با عملگرهای عام

در ادامه درباره جمع شرطی با تطبیق جزئی همراه با عملگرهای عام صحبت می‌کنیم:

• علامت سؤال (؟) برای مطابقت با هر کاراکتر در یک موقعیت خاص استفاده می‌شود.
• ستاره (*) برای مطابقت با هر تعداد کاراکتر است.

مثال 1: جمع مقادیر بر حسب تطبیق جزئی

فرض کنید می‌خواهید مجموع فروش را برای تمام مناطق تهران، شامل تهران-مرکز، تهران-غرب، تهران-شرق و تهران-جنوب، به‌دست بیاورید. برای انجام این کار، بعد از کلمه «تهران» از کاراکتر ستاره (*) استفاده کنید:

1=SUMIF(B2:B10, "تهران*", C2:D10)
2

قرار دادن علامت ستاره در هر دو طرف کلمه «تهران» نیز همین کار را خواهد کرد:

1=SUMIF(B2:B10, "*تهران*", C2:D10)
2

همچنین می‌توانید نام شهر مورد نظر را در یک سلول خالی مانند F1 وارد و سپس در فرمول خود از آن استفاده کنید. به‌این ترتیب، فرمول به شکل زیر خواهد شد:

1=SUMIF(B2:B10, F1&"*", C2:C10)
2

یا

1=SUMIF(B2:B10, "*"&F1&"*", C2:C10)

همان‌طور که در تصویر می‌بینید، تمام سلول‌های حاوی عبارت «تهران» به‌غیر از سلول‌های «شمال» و «شرق»، جمع شده‌اند.

مثال 2: شرایطی که سلول SUMIF‌ حاوی (؟) یا (*) است

ممکن است مقداری که در سلول نوشته شده است، همراه خود علامت (؟) یا (*) را داشته باشد. در چنین حالتی از علامت (~) قبل از آن اسفاده کنید ("؟~" یا "*~").

به‌عنوان مثال، در ادامه برای جمع فروش مناطقی که با (*) علامت‌گذاری شده‌اند، از "*~*" استفاده می‌کنیم. در این مثال، (*) اول، کاراکتر عام و (*) دوم، مقداری است که در سلول وجود داشته است:

1=SUMIF(B2:B10, "*~*", C2:D10)
2

اگر شرط،‌ در این مثال (*)، در سلول مجزایی نوشته شده باشد، علامت (~) و مرجع سلول را با (&) به‌هم متصل کنید:

1=SUMIF(B2:B10, "*"&"~"&F1, C2:D10)

مثال 3: SUMIF در شرایطی که سلول متناظر شامل متن باشد

اگر مجموعه داده شامل انواع مختلفی از داده‌ها باشد و بخواهید فقط سلول‌هایی را جمع کنید که با یک مقدار متنی متناظر هستند، فرمول SUMIF به شکل زیر خواهد شد‌ (یعنی جمع مقادیر سلول‌های C2:C10، اگر سلول متناظر در ستون محصولات، هر کاراکتر متنی‌ای داشت):

1=SUMIF(A2:A10,"?*", C2:C10)
2

برای جمع کردن مقادیر سلول‌های C2:C10، اگر سلول متناظر در ستون محصولات، حاوی هر مقدار متنی، از جمله رشته‌هایی با طول 0 بود، از فرمول به شکل زیر استفاده کنید:

1=SUMIF(A2:A8,"*", C2:C10)
2

همان‌طور که در تصویر زیر می‌بینید، نام چند محصول را به عدد تغییر دادیم. در این شرایط، هر دو فرمول بالا مقادیر غیر متنی یعنی اعداد، تاریخ، خطاها و بولین‌ها (مقادیر دو حالتی مانند "بله" و "خیر") را نادیده گرفته و سلول‌های متناظری که حاوی متن هستند را در جمع حساب می‌کنند.

جمع شرطی چند برگه در اکسل

اگر داده‌هایی در چند «برگه» (Sheet) اکسل دارید، می‌توانید از SUMIF برای جمع آن‌ها استفاده کنید، اما چند روش مختلف برای این کار وجود دارد. در این مقاله سه روش را به شما معرفی خواهیم کرد که به کمک آن‌ها می‌توانید SUMIF را در چند برگه یا شیت استفاده کنید.

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

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

فرض کنید می‌خواهید جمع فروش سالانه هر منطقه را در برگه‌ای به نام «فروش کل» به‌دست آورید. برای این کار، فرمول زیر را در سلول B2 بنویسید:

1=SUMIF(بهار!A2:A5,'فروش کل'!A2,بهار!B2:B5)+SUMIF(تابستان!A2:A5,'فروش کل'!A2,تابستان!B2:B5)+SUMIF(پاییز!A2:A5,'فروش کل'!A2,پاییز!B2:B5)+SUMIF(زمستان!A2:A5,'فروش کل'!A2,زمستان!B2:B5)

• در این قسمت، بهار!A2:A5   برابر «محدوده» در برگه «بهار» است، یعنی جایی که شرط باید محقق شود.
• 'فروش کل'!A2   شرط است.
• بهار!B2:B5  ، «محدوده جمع» در برگه «بهار» است. یعنی جایی که مقادیر مورد نیاز برای جمع، قرار دارند.

به همین شکل می‌توانید فرمول جمع شرطی را برای تمام شیت‌های دیگر استفاده کنید. با زدن دکمه Enter، مجموع فروش هر سه فصل منطقه «تهران-شمال» را در سلول B2 خواهید داشت. همین کار را برای منطقه‌های دیگر هم انجام دهید تا فروش سالیانه آن‌ها به‌دست آید.

روش دوم: استفاده از توابع SUMPRODUCT، SUMIF و INDIRECT

به‌جای اینکه تابع SUMIF را چندین بار تکرار کنید، می‌توانید توابع SUMPRODUCT ،SUMIF و INDIRECT را در ترکیب با هم به‌کار ببرید و به نتیجه مشابه برسید. در ابتدا نام شیت‌ها (بهار، تابستان، پاییز و زمستان) را در شیتی که می‌خواهید جمع سالانه را در آن انجام دهید، وارد کنید. سپس فرمول زیر را در سلول B2 بنویسید:

1=SUMPRODUCT(SUMIF(INDIRECT("'"&$E$2:$E$5&"'!$A$2:$A$5"),A2,INDIRECT("'"&$E$2:$E$5&"'!$B$2:$B$5")))

• در اینجا، &$E$2:$E$5&   اشاره به شیت‌های مختلفی دارد که مقادیر فروش ماهانه درون آن‌ها است.
• !$A$2:$A$5   «محدوده» موردنظر برای تطبیق با شرط است.
• A2   شرط است.
• '!$B$2:$B$5   هم «محدوده جمع» محسوب می‌شود.

بعد از فشردن دکمه Enter، جمع فروش فصلی «تهران-شمال» در سلول B2 نشان داده خواهد شد. مانند قبل،‌ همین فرمول را برای باقی منطقه‌ها هم بنویسید تا جمع فروش سالانه آن‌ها محاسبه شود.

روش سوم: استفاده از VBA برای انجام جمع شرطی در شیت های مختلف اکسل

VBA یا (Visual Basic for Applications) یک زبان برنامه‌نویسی است که توسط مایکروسافت توسعه داده شده است. در شرایطی که شیت‌های مختلف زیادی داشته باشید، استفاده از دو روش بالا می‌تواند زمان‌بر و پیچیده باشد. به‌همین دلیل برای ساده‌تر و سریع‌تر کردن محاسبه، می‌توانید با ماکروهای VBA در اکسل فرمول شخصی جدیدی بسازید.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

در ابتدا دکمه‌های ALT+F11 را روی کیبورد فشار دهید تا پنجره VBA باز شود. روی نام برگه (خلاصه فروش) راست کلیک کرده و سپس Insert > Module را بزنید.

در این مرحله پنجره‌ای برای ورود کد باز می‌شود. کد زیر را کپی کرده و در این پنجره جای‌گذاری (Paste) کنید.

1Function SUMIFOS(lookup_value As Range, ParamArray cellranges() As Variant)
2
3Dim i As Integer, rng1 As Variant, temp As Single, a As Boolean
4
5Dim rng2 As Variant, value As Variant, j As Single
6
7If (UBound(cellranges) + 1) Mod 2 <> 0 Then
8
9  Exit Function
10
11End If
12
13For i = LBound(cellranges) To UBound(cellranges) Step 2
14
15    If cellranges(i).Rows.Count <> cellranges(i + 1).Rows.Count Then
16
17    End If
18
19    If cellranges(i).Columns.Count <> 1 Then
20
21        Exit Function
22
23End If
24
25    rng1 = cellranges(i).value
26
27    rng2 = cellranges(i + 1).value
28
29    For j = LBound(rng1) To UBound(rng1)
30
31        For Each value In lookup_value
32
33            If UCase(rng1(j, 1)) = UCase(value) Then a = True
34
35        Next value
36
37        If a = True Then temp = temp + rng2(j, 1)
38
39        a = False
40
41    Next j
42
43Next i
44
45SUMIFOS = temp
46
47End Function

سپس پنجره VBA را بسته و فرمول زیر را در سلول B2 وارد کنید:

1=SUMIFOS(A2,بهار!A2:A6,بهار!B2:B6,تابستان!A2:A6,تابستان!B2:B6,پاییز!A2:A6,پاییز!B2:B6,زمستان!A2:A6,زمستان!B2:B6)

• در این قسمت، SUMIFOS   تابع شخصی‌سازی شده است.
• A2   مقدار مورد جست‌وجو یا شرط را نشان می‌دهد.
• بهار!A2:A6   «محدوده» در شیت «بهار» است.
• و بهار!B2:B6   «محدوده جمع» در شیت «بهار» را نشان می‌دهد.

شما می‌توانید مقدار هر تعداد شیت که می‌خواهید را در این فرمول وارد کنید. بعد از زدن Enter، نتیجه نهایی جمع فروش تمام فصول «تهران-شمال» را در سلول B2 خواهید دید.

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

استفاده از تاریخ به‌عنوان شرط در SUMIF‌ بسیار شبیه استفاده از اعداد است. البته در این قسمت، قالب بندی سلول در اکسل یا (Format Cell) بسیار اهمیت دارد. مهم‌ترین کار این است که «قالب‌بندی» (Format) سلول را بر مبنای تاریخ تنظیم کنید تا اکسل آن را با قالب درست در نظر بگیرد. اگر با نحوه مدیریت تاریخ در اکسل آشنا نیستید، می‌توانید از راهنمای ساعت و زمان در اکسل کمک بگیرید.

تصور کنید می‌خواهید جمع مواردی را به دست بیاورید که پیش از تاریخ 06/23 به دست مشتری رسیده‌اند. در این حالت، شرط به شکل زیر خواهد بود:

1=SUMIF(C2:C10, "<06/23", B2:B10)
2

یا

1=SUMIF(C2:C10, "<"&DATE(06,23), B2:B10)
2

یا

1=SUMIF(C2:C10, "<"&F1, B2:B10)
2

آخری در صورتی است که تاریخ مورد نظر در سلول F1 نوشته شده باشد.

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

1=SUMIF(C2:C10, "<"&TODAY(), B2:B10)

جمع شرطی بین دو تاریخ در اکسل

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

به‌عنوان مثال، برای جمع مقادیری از ستون «تعداد فروخته‌شده»، در شرایطی که تاریخی خاص در ستون «زمان ارسال» و بین دو تاریخ قرار داشته باشد، فرمول را به شکل زیر می‌نویسیم:

1=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)
2

در این فرمول، B2:B10 «محدوده جمع» است، C2:C10 بازه زمانی است که می خواهیم چک کنیم و همچنین، F1 تاریخ شروع و G2 تاریخ پایان هستند.

آشنایی با تابع SUMIFS در اکسل

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

1SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )

• Sum-Range یا «محدوده جمع»: سلول‌هایی که می‌خواهید جمع کنید را نشان می‌دهد.
• Criteria_Range1 یا «محدوده1»: محدوده‌ای از سلول‌ها است که می‌خواهید «شرط1» را روی آن‌ها اعمال کنید.
• Criteria1 یا شرط 1: شرط اول که تعیین می‌کند کدام سلول‌ها اضافه شوند. این شرط روی سلول‌های «محدوده1» اعمال می‌شود.
• Criteria2, ... Criteria_n یا شرط 2 الی شرط n: این مورد اختیاری است و نشان‌دهنده شرط‌های دوم به بعد است. این شروط برای تعیین سلول‌هایی که باید جمع شوند، استفاده می‌شوند. «شرط 2» روی «محدوده 2»، «شرط 3» روی «محدوده 3» و همین‌طور به‌ترتیب، هر شرط روی محدوده خود اعمال می‌شود. می‌توانید تا 127 شرط به این فرمول اضافه کنید.

نحوه استفاده از تابع جمع شرطی از یک «شیت» (Sheet) دیگر

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

به‌عنوان مثال، فرمول زیر مقادیر موجود در سلول‌های C2 تا C10 را که در شیت «آمار فروش» هستند، با شرط B2 که در شیت «گزارشات» است، جمع می‌کند.

1=SUMIF(آمارفروش!B2:B10, B2, آمارفروش!C2:C10)

نحوه درست استفاده از ارجاعات سلولی در شرط SUMIF

معمولا برای نوشتن یک فرمول منعطف، بهتر است به‌جای اینکه پارامترهای متغیر را مستقیما در کد بنویسید، آن‌ها را در یک سلول خاص وارد کرده و سپس به آن ارجاع دهید (مانند مثال‌های حاوی سلول F1 در این مقاله). البته ممکن است انجام این کار با فرمول جمع شرطی در اکسل کمی سخت باشد.

مثلا همانطور که در ادامه می‌بینید، برای جمع کردن با شرط برابری، استفاده از ارجاع سلولی ساده است:

1=SUMIF(C2:C10, F1, B2:B10)
2

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

1=SUMIF(C2:C10, ">"&F1, B2:B10)
2

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

حل مشکل کار نکردن تابع جمع شرطی در اکسل

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

فرمول SUMIF در اکسل فقط از یک شرط پشتیبانی می‌کند

فرمول تابع SUMIF تنها برای یک شرط جا دارد. برای ترکیب چند شرط یا باید از تابع SUMIFS استفاده کنید (تا سلول‌هایی جمع زده شوند که تمام شرط‌ها را دارند) یا می‌توانید فرمول جمع شرطی‌ای بسازید که چند شرط با OR دارد (تا سلول‌هایی جمع زده شوند که هر کدام از شروط را برآورده کرده باشند).

«محدوده» و «محدوده جمع» باید یک اندازه باشند

برای اینکه یک تابع جمع شرطی در اکسل به‌درستی کار کند، آرگومان‌های «محدوده» (Range) و «محدوده جمع» (Sum-range) باید ابعاد یکسانی داشته باشند، در غیر اینصورت نتیجه به‌درستی نشان داده نمی‌‌شود. نکته اینجاست که مایکروسافت اکسل به توانایی مخاطب در ارائه محدوده‌های منطبق با یکدیگر تکیه نمی‌کند تا از بروز ناسازگاری‌های احتمالی، پیشگیری به‌عمل آورد. به همین دلیل، «محدوده جمع» را به‌صورت خودکار به‌شکل زیر حساب می‌کند:

آرگومان «محدوده جمع»، تنها اولین سلول بالا و سمت چپ محدوده‌ای را تعیین می‌کند که قرار است برای جمع مورد استفاده قرار گیرد. باقی موارد به کمک اندازه و ظاهر آرگومان «محدوده» تعیین خواهند شد.

با توجه به این مورد، فرمول زیر در واقع سلول‌های بین C2 تا C10 را جمع خواهد کرد و نه C2 تا D10. چون «محدوده»، یک ستون و نه ردیف دارد، پس «محدوده جمع» هم باید همین‌طور باشد.

1=SUMIF(B2:B10, "تهران", C2:D10)
2

عدم برابری محدوده‌ها، در نسخه‌های قدیمی اکسل، مشکلات زیادی به‌وجود می‌آورد. در اکسل مدرن، وجود SUMIFهای پیچیده که «محدوده جمع» با ستون‌ها و/یا ردیف‌های کمتری از «محدوده» داشته‌باشند، همچنان مشکل‌ساز است. به‌همین دلیل مهم است که تلاش کنید همیشه تعداد ستون‌ها و ردیف‌های یکسانی برای این دو آرگومان تعریف کنید.

«محدوده» و «محدوده جمع» باید محدوده باشند نه آرایه

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

نحوه نوشتن شرط های SUMIF

در قسمت «شرط» تابع جمع شرطی، استفاده از انواع مختلف داده شامل متن، عدد، تاریخ، ارجاع سلولی، عملگرهای منطقی (> ،< ،= ،<>)، عملگرهای عام (؟ ،* ،~) و سایر توایع، امکان‌پذیر است. ظاهر چنین شرطی کاملا خاص خواهد بود.

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

1=SUMIF(B2:B10, "تهران*", C2:D10)
2

1=SUMIF(C2:D10, ">100")
2

1=SUMIF(B2:B10, "<>تهران", C2:D10)
2

1=SUMIF(C2:C10, "<=06/23/2020", B2:B10)
2

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

1=SUMIF(B2:B10, ">"&F2)
2

1=SUMIF(C2:D10, "<="&TODAY(), B2:B10)
2

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

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

مثلا فرمول زیر تا زمانی که File1 باز باشد، به‌درستی کار می‌کند:

1=SUMIF([File1.xlsx]Sheet1!$A$2:$A$9,"apples",[Book1.xlsx]Sheet1!$F$2:$F$9)
2

اما به‌محض بسته شدن این فایل، فرمول هم از کار خواهد افتاد. این اتفاق به این دلیل رخ می‌دهد که محدوده‌های ارجاع داده‌شده به فایلی که بسته شد، به ارجاع به آرایه تبدیل می‌شوند. سپس از آنجایی که دو آرگومان «محدوده» و «محدوده جمع» در تابع جمع شرطی، آرایه را به‌عنوان ورودی نمی‌پذیرند، SUMIF خطای #VALUE! را نشان خواهد داد.

 فرمول SUMIF در اکسل متن را به‌درستی تشخیص نمی‌دهد

از نظر طراحی، SUMIF به بزرگی و کوچکی حروف انگلیسی حساس نیست. یعنی با حروف بزرگ و کوچک به‌عنوان کاراکترهای یکسان برخورد می‌کند. برای عوض کردن این حالت و تبدیل SUMIF به فرمولی حساس به بزرگی و کوچکی حروف، می‌توانید دو تابع SUMPRODUCT و EXACT را در ترکیب با هم به‌کار ببرید.

سخن نهایی

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