=SUMPRODUCT(B2:B6,C2:C6) מכפילה כל כמות ב-B במחיר שלידה ב-C, ואז מחברת את התוצאות. היא נותנת את סך ההזמנה בתא אחד, בלי עמודה של סכומי שורות.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Line total | Total | |
| 2 | Pen | 4 | $1.50 | $6.00 | $30.70 | |
| 3 | Notebook | 2 | $3.25 | $6.50 | $30.70 | |
| 4 | Folder | 5 | $0.80 | $4.00 | ||
| 5 | Stapler | 1 | $7.90 | $7.90 | ||
| 6 | Marker | 3 | $2.10 | $6.30 |
F2 ו-F3 מציגים את אותו $30.70. סכומי השורות בעמודה D נמצאים שם רק כדי להראות מה SUMPRODUCT עושה: 4 × 1.50, 2 × 3.25 וכן הלאה, ואז SUM. שנו כמות ושני הסכומים מתעדכנים.
התחביר של SUMPRODUCT
=SUMPRODUCT(array1, [array2], [array3], ...)
- כל מערך הוא טווח או חישוב שיוצר טווח, וכולם חייבים להיות באותו גודל, אחרת SUMPRODUCT מחזירה
#VALUE!. - עם שני מערכים או יותר, הערכים שבאותו מיקום מוכפלים, ואז המכפלות מתחברות.
- עם מערך אחד היא פשוט מחברת אותו, וזה מה שגורם לצורות התנאי שלמטה לעבוד: ב-
=SUMPRODUCT((A2:A7="North")*C2:C7)יש מערך אחד, שכבר הוכפל. - טקסט שמועבר כארגומנט נפרד נחשב 0. טקסט בתוך חישוב עם
*גורם ל-#VALUE!.
SUMPRODUCT עובדת עם מערכים בכל גרסה של אקסל בלי Ctrl+Shift+Enter (Cmd+Shift+Enter ב-Mac), ולכן היא הייתה הכלי הסטנדרטי לסכומים עם תנאי לפני ש-SUMIFS הופיעה, ועדיין היא הכלי למקרים ש-SUMIFS לא מטפלת בהם.
SUMPRODUCT עם תנאים
השוואה על טווח, A2:A7="North", מחזירה TRUE או FALSE אחד לכל שורה. הכפלה בה משאירה את השורות שבהן היא TRUE (×1) ומאפסת את האחרות (×0). הכפילו שתי השוואות כדי לקבל AND.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | North sales | 230 | |
| 3 | South | Pear | 45 | North Apple sales | 150 | |
| 4 | North | Pear | 80 | Count North | 3 | |
| 5 | East | Apple | 55 | Count over 50 | 4 | |
| 6 | South | Apple | 200 | Without -- | 0 | |
| 7 | North | Apple | 30 |
F2 מחבר את שלוש השורות של North: 230. F3 מכפיל שני תנאים, ולכן שורה נספרת רק כששניהם מתקיימים: 150. כדי לספור במקום לסכם, השמיטו את הערכים והפכו את ה-TRUE/FALSE למספרים עם -- (שני סימני מינוס): F4 סופר 3 שורות של North. F6 מראה למה ה--- חשוב: SUMPRODUCT לא מחברת ערכי TRUE, ולכן הנוסחה בלעדיו מחזירה 0.
ארבע הראשונות נותנות את אותן תוצאות כמו SUMIF, SUMIFS ו-COUNTIF. הסעיף הבא הוא המקום שבו SUMPRODUCT מצדיקה את קיומה.
תנאים ש-SUMIFS לא יכולה לבטא
SUMIFS משווה עמודה לקריטריון קבוע. היא לא יכולה לקחת את החודש של תאריך, להשוות שתי עמודות זו לזו, או להכפיל כמות במחיר לפני החיבור. SUMPRODUCT יכולה, כי כל תנאי הוא חישוב רגיל.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Date | Target | Actual | Formula | Result | |
| 2 | North | 2026-01-05 | 100 | 120 | February sales | 135 | |
| 3 | South | 2026-01-12 | 60 | 45 | Rows over target | 3 | |
| 4 | North | 2026-02-03 | 90 | 80 | North or East sales | 285 | |
| 5 | East | 2026-02-18 | 50 | 55 | Above target by | 75 | |
| 6 | South | 2026-03-02 | 150 | 200 | |||
| 7 | North | 2026-03-20 | 40 | 30 |
- G2 לוקח את ה-MONTH של כל תאריך ומשאיר את השורות של פברואר: 80 + 55 = 135. זה מחבר את פברואר של כל שנה; הוסיפו
*(YEAR(B2:B7)=2026)לשנה אחת בלבד. - G3 משווה שתי עמודות שורה מול שורה וסופר את השורות שבהן Actual עובר את Target.
- G4 הוא OR: חיבור של שני תנאים נותן 1 כשאחד מהם מתקיים (ו-2 כששניהם, ולכן ה-
>0נמצא שם).NorthאוEast:285. - G5 מחבר בכמה כל שורה עברה את היעד שלה, רק עבור השורות שעברו.
SUMPRODUCT לסכומים ולממוצעים משוקללים
כמות כפול מחיר היא סכום משוקלל, ואפשר להוסיף לו תנאים. אותו רעיון מחולק בסכום המשקלים נותן ממוצע משוקלל: =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6) הוא המחיר הממוצע לפריט שנמכר.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Qty | Price | Formula | Result | |
| 2 | North | Apple | 10 | $1.20 | North revenue | $31.00 | |
| 3 | South | Pear | 4 | $1.50 | All revenue | $61.00 | |
| 4 | North | Pear | 6 | $1.50 | Average price per item | $1.36 | |
| 5 | East | Apple | 8 | $1.20 | |||
| 6 | South | Apple | 12 | $1.20 | |||
| 7 | North | Plum | 5 | $2.00 |
North מכר 10 תפוחים ב-$1.20, 6 אגסים ב-$1.50 ו-5 שזיפים ב-$2.00, ולכן G2 מציג $31.00. ממוצע פשוט של המחירים היה מתייחס לשזיף כאילו נמכר באותה תדירות כמו תפוח; G4 משקלל כל מחיר לפי הכמות שלו.
תרגול: הכנסות עם תנאי
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Qty | Price | Formula | Result | |
| 2 | North | Apple | 10 | $1.20 | South revenue | ||
| 3 | South | Pear | 4 | $1.50 | |||
| 4 | North | Pear | 6 | $1.50 | |||
| 5 | East | Apple | 8 | $1.20 | |||
| 6 | South | Apple | 12 | $1.20 | |||
| 7 | North | Plum | 5 | $2.00 |
תורכם: חשבו את ההכנסות של South: כמות כפול מחיר, רק עבור השורות של South. כתבו את הנוסחה ב-G2.
SUMPRODUCT מול SUMIFS, ושתי השגיאות שלה
| תנאי | SUMIFS | SUMPRODUCT |
|---|---|---|
| עמודה שווה לערך | =SUMIFS(C2:C7,A2:A7,"North") | =SUMPRODUCT((A2:A7="North")*C2:C7) |
| מכיל טקסט | =SUMIFS(C2:C7,B2:B7,"*app*") | =SUMPRODUCT(ISNUMBER(SEARCH("app",B2:B7))*C2:C7) |
| החודש של תאריך | לא אפשרי ישירות | =SUMPRODUCT((MONTH(B2:B7)=2)*D2:D7) |
| עמודה מול עמודה | לא אפשרי | =SUMPRODUCT(--(D2:D7>C2:C7)) |
| כמות × מחיר | לא אפשרי | =SUMPRODUCT(C2:C7,D2:D7) |
העדיפו את SUMIFS בכל פעם שהיא יכולה לעשות את העבודה. היא קריאה יותר, מהירה יותר על עשרות אלפי שורות, ומקבלת עמודות שלמות. =SUMPRODUCT((A:A="North")*C:C) מכפילה על פני יותר ממיליון שורות ומחזירה #VALUE! ברגע שהיא מגיעה לטקסט הכותרת ב-C1, ולכן תנו ל-SUMPRODUCT טווחים מדויקים כמו A2:A500.
שתי השגיאות שתפגשו:
#VALUE!מטווחים בגדלים שונים.=SUMPRODUCT(B2:B6,C2:C7)נכשלת. כל טווח חייב לכסות את אותן שורות.#VALUE!מטקסט בטווח שמוכפל. כותרת אוn/aבתוךC2:C7שוברים את(A2:A7="North")*C2:C7, כי אי אפשר להכפיל טקסט. התחילו את הטווח מתחת לכותרת, או העבירו את הערכים כארגומנט נפרד:=SUMPRODUCT(--(A2:A7="North"),C2:C7)מתייחסת לטקסט ב-C כ-0.
שאלות נפוצות
מה SUMPRODUCT עושה באקסל?
היא מכפילה טווחים שורה מול שורה ומחברת את המכפלות. =SUMPRODUCT(B2:B6,C2:C6) היא B2C2 + B3C3 + ... + B6*C6, למשל כמות כפול מחיר שמסתכמים לסך ההזמנה.
איך משתמשים ב-SUMPRODUCT עם תנאי?
הכפילו בהשוואה: =SUMPRODUCT((A2:A7="North")*C2:C7) מחברת את C2:C7 עבור השורות של North. ההשוואה נותנת TRUE או FALSE, שהופכים ל-1 או 0 כשמכפילים אותם.
מה המשמעות של -- ב-SUMPRODUCT?
אלה שני סימני מינוס, שהופכים את TRUE ו-FALSE ל-1 ו-0. =SUMPRODUCT(--(C2:C7>50)) סופרת את הערכים שמעל 50. בלעדיהם, SUMPRODUCT מתייחסת ל-TRUE/FALSE כ-0 ומחזירה 0.
האם להשתמש ב-SUMPRODUCT או ב-SUMIFS?
השתמשו ב-SUMIFS כשהקריטריונים שלה יכולים לבטא את התנאי: היא קריאה יותר ומהירה יותר על טווחים גדולים. השתמשו ב-SUMPRODUCT כשהתנאי דורש חישוב, כמו החודש של תאריך, עמודה אחת מול אחרת, או כמות כפול מחיר.
למה SUMPRODUCT מחזירה #VALUE!?
לטווחים יש גדלים שונים (B2:B6 עם C2:C7), או שטווח שמוכפל עם * מכיל טקסט. ודאו שכל הטווחים באותו גודל, והעבירו טווחים עם טקסט כארגומנטים נפרדים, שבהם טקסט נחשב 0.
