SUMIF, SUMIFS, AVERAGEIF ו-AVERAGEIFS מסכמות או ממוצעות רק את השורות שעומדות בתנאים שלך. הן הופכות רשימה ארוכה למספרים שדוח צריך, בלי סינון ובלי עמודות עזר.
דוגמה מעובדת
קח רשימה של מכירות. ב-A2:A6 מופיע הנציג (אנה, בן, אנה, סי, בן), וב-B2:B6 הסכום (100, 250, 80, 40, 120).
=SUMIF(A2:A6,"Ana",B2:B6)
מחבר את הסכומים שבהם הנציג הוא אנה: 100 + 80 = 180.
=SUMIFS(B2:B6,A2:A6,"Ben",B2:B6,">=200")
הסכומים של בן שהם לפחות 200: רק 250.
=AVERAGEIF(A2:A6,"Ben",B2:B6)
הממוצע של הסכומים של בן: (250 + 120) / 2 = 185.
המלכודת של סדר הארגומנטים
שתי המשפחות מסדרות את הארגומנטים שלהן בצורה שונה. ב-SUMIF טווח הסכימה בא אחרון, ואפשר להשמיט אותו כשהטווח שבודקים הוא גם הטווח שמחברים. ב-SUMIFS טווח הסכימה בא ראשון, ואחריו זוגות של טווח ותנאי. AVERAGEIF ו-AVERAGEIFS פועלות לפי אותן שתי תבניות.
תנאי על המספרים עצמם לא צריך טווח שני: =SUMIF(B2:B6,">100") מחבר את 250 ואת 120, ולכן מחזיר 370.
וריאציות שימושיות
תנאים בתאים. שים "אנה" ב-F1 וכתוב =SUMIF(A2:A6,F1,B2:B6). כל אחד יכול לשנות את הדוח בהקלדת שם חדש.
תאריכים. כדי לסכם הכול מתאריך התחלה ב-F2 והלאה, חבר את האופרטור והתא: =SUMIFS(B2:B6,C2:C6,">="&F2), כאשר ב-C2:C6 מופיעים התאריכים.
תווים כלליים. קריטריונים כמו "א*" עובדים גם כאן, בדיוק כמו ב-COUNTIF.
OR לוגי. חבר שתי תוצאות: =SUMIF(A2:A6,"אנה",B2:B6)+SUMIF(A2:A6,"סי",B2:B6).
בניית טבלת סיכום
היתרון האמיתי של הפונקציות האלה מופיע כשרושמים את הקטגוריות פעם אחת, ונותנים לנוסחה אחת לעשות את העבודה לכל אחת. הקלד את הנציגים ב-D2:D4 (אנה, בן, סי) וכתוב את זה ב-E2, ואחר כך העתק למטה:
=SUMIF($A$2:$A$6,D2,$B$2:$B$6)
הסימנים הדולר מקבעים את טווחי הנתונים, בזמן ש-D2 זז ל-D3 ול-D4. התוצאות הן 180, 370 ו-40.
שני טריקים נוספים עוזרים כאן. =SUMIF(A2:A6,"<>אנה",B2:B6) מחבר את כולם חוץ מאנה (410). וחלוקה של סכום ב-SUM של כל העמודה נותנת את החלק של כל נציג: =E2/SUM($B$2:$B$6).
כשזה משתבש
אם אף שורה לא מתאימה, SUMIF מחזירה 0, וזו תשובה כנה. AVERAGEIF אין מה לחלק, ולכן =AVERAGEIF(A2:A6,"זד",B2:B6) מחזירה #DIV/0!. עטוף אותה ב-IFERROR או בדוק קודם עם COUNTIF.
בפונקציות מסוג ה-S (SUMIFS ו-AVERAGEIFS), כל הטווחים חייבים להיות באותו גודל, אחרת התוצאה היא #VALUE!. זכור גם שסכומים שמאוחסנים כטקסט מדולגים בשקט. אם סך נראה קטן מדי, בדוק שהמספרים הם מספרים אמיתיים.