عمومی، کاربردی ۵۶ بازدید

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

فهرست مطالب این نوشته

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

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

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

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

SUMIF(range, criteria, [sum_range])

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

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

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

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

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

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

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

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

یا

=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 روز آینده و بعد از آن ارسال خواهند شد. برای نمایش این موقعیت باید از یک علامت (<) قبل از عدد خود استفاده کرده و ساختار کلی شرط را درون ("") قرار دهید.

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

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

=SUMIF(C2:C10, ">"&F1, B2:B10)
عملگر بزرگ تر در SUMIF

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

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

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

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

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

یا

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

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

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

شرط برابری در SUMIF

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

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

=SUMIF(A2:A10, "جاروبرقی", B2:B10)
=SUMIF(A2:A10, "=جاروبرقی", B2:B10)
=SUMIF(A2:A10, F1, B2:B10)
شرط برابری با متن

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

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

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

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

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

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

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

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

=SUMIF(A2:A10, "<>جاروبرقی", B2:B10)
شرط عدم برابری در SUMIF

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

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

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

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

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

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

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

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

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

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

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

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

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

=SUMIF(B2:B10, "<>", C2:D10)
جمع شرطی در اکسل برای سلول های جاوی مقدار

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

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

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

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

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

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

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

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

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

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

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

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

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

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

یا

=SUMIF(B2:B10, "*"&F1&"*", C2:C10)
استفاده از تطبیق نسبی در فرمول جمع شرطی در اکسل

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

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

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

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

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

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

=SUMIF(B2:B10, "*"&"~"&F1, C2:D10)
جمع شرطی در اکسل برای سلول حاوی عملگر عام

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

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

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

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

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

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

حمع سلول های حاوی متن

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

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

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

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

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

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

=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 خواهید داشت. همین کار را برای منطقه‌های دیگر هم انجام دهید تا فروش سالیانه آن‌ها به‌دست آید.

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

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

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

=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   هم «محدوده جمع» محسوب می‌شود.
مطلب پیشنهادی:
آدرس دهی در اکسل با تابع INDIRECT — راهنمای کاربردی
تابع INDIRECT برای آدرس دهی در اکسل به کار رفته و آدرس سلول را از کابر دریافت کرده و محتویات آن را با توجه به شیوه آدرس‌دهی A1 یا R1C1 نمایش می‌دهد.

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

جمع شرطی چند شیت با فرمول SUMPRODUCT

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

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

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

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

Function SUMIFOS(lookup_value As Range, ParamArray cellranges() As Variant)

Dim i As Integer, rng1 As Variant, temp As Single, a As Boolean

Dim rng2 As Variant, value As Variant, j As Single

If (UBound(cellranges) + 1) Mod 2 <> 0 Then

  Exit Function

End If

For i = LBound(cellranges) To UBound(cellranges) Step 2

    If cellranges(i).Rows.Count <> cellranges(i + 1).Rows.Count Then

    End If

    If cellranges(i).Columns.Count <> 1 Then

        Exit Function

End If

    rng1 = cellranges(i).value

    rng2 = cellranges(i + 1).value

    For j = LBound(rng1) To UBound(rng1)

        For Each value In lookup_value

            If UCase(rng1(j, 1)) = UCase(value) Then a = True

        Next value

        If a = True Then temp = temp + rng2(j, 1)

        a = False

    Next j

Next i

SUMIFOS = temp

End Function

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

=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 به دست مشتری رسیده‌اند. در این حالت، شرط به شکل زیر خواهد بود:

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

یا

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

یا

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

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

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

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

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

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

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

مطلب پیشنهادی:
تاریخ شمسی در اکسل | راهنمای کاربردی
در این نوشتار با تغییر فرمت سلول برای نمایش تاریخ شمسی در اکسل آشنا شدیم. همچنین نحوه محاسبات روی تاریخ شمسی نیز معرفی و به کار گرفته شد.

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

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

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

بازه زمانی در SUMIF

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

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

SUMIFS( 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 که در شیت «گزارشات» است، جمع می‌کند.

=SUMIF(آمارفروش!B2:B10, B2, آمارفروش!C2:C10)
استفاده از داده های شیت دیگر در SUMIF

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

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

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

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

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

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

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

ارجاع دهی درست در SUMIF

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

سخن نهایی

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

اگر این مطلب برای شما مفید بوده است، آموزش‌ها و مطالب زیر نیز به شما پیشنهاد می‌شوند:

بر اساس رای ۷ نفر
آیا این مطلب برای شما مفید بود؟
شما قبلا رای داده‌اید!
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.

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