כיצד להשתמש ב-SUMIF ב-Google Sheets

מדריך זה מספק הדגמה מפורטת כיצד להשתמש בפונקציות SUMIF ו-SUMIFS ב-Google Sheets עם נוסחאות ודוגמאות.

SUMIF היא אחת הפונקציות המתמטיות ב-Google Sheets, המשמשת לסיכום תאים באופן מותנה. בעיקרון, הפונקציה SUMIF מחפשת תנאי ספציפי בטווח של תאים ולאחר מכן מחברת את הערכים העונים לתנאי הנתון.

לדוגמה, יש לך רשימה של הוצאות ב-Google sheets ואתה רוצה לסכם רק את ההוצאות שנמצאות מעל ערך מקסימלי מסוים. או שיש לך רשימה של פריטי הזמנה והסכומים התואמים שלהם, ואתה רוצה לדעת רק את כמות ההזמנה הכוללת של פריט ספציפי. זה המקום שבו פונקציית SUMIF שימושית.

ניתן להשתמש ב-SUMIF לסיכום ערכים על סמך מצב מספר, מצב טקסט, מצב תאריך, תווים כלליים וכן על סמך תאים ריקים ולא ריקים. ל-Google Sheets יש שתי פונקציות לסיכום ערכים על סמך קריטריונים: SUMIF ו-SUMIFS. פונקציית SUMIF מחברת מספרים על סמך תנאי אחד בעוד ש-SUMIFS מסכם מספרים על סמך מספר תנאים.

במדריך זה, נסביר כיצד להשתמש בפונקציות SUMIF ו-SUMIFS ב-Google Sheets כדי לסכם מספרים העומדים בתנאים מסוימים.

פונקציית SUMIF ב-Google Sheets - תחביר וארגומנטים

הפונקציה SUMIF היא רק שילוב של הפונקציה SUM ו-IF. הפונקציה IF סורקת את טווח התאים עבור תנאי נתון, ולאחר מכן הפונקציה SUM מסכמת את המספרים המתאימים לתאים העומדים בתנאי.

תחביר של פונקציית SUMIF:

התחביר של פונקציית SUMIF ב-Google Sheets הוא כדלקמן:

=SUMIF(טווח, קריטריון, [טווח_סכום])

טיעונים:

טווח - טווח התאים שבו אנו מחפשים את התאים העומדים בקריטריונים.

קריטריונים – הקריטריונים שקובעים אילו תאים יש להוסיף. אתה יכול לבסס את הקריטריון על המספר, מחרוזת טקסט, תאריך, הפניה לתא, ביטוי, אופרטור לוגי, תו כללי כמו גם פונקציות אחרות.

טווח_sum – טיעון זה הוא אופציונלי. זהו טווח הנתונים עם ערכים לסיכום אם ערך הטווח המתאים תואם לתנאי. אם לא תכלול טיעון זה, ה'טווח' יסוכם במקום זאת.

כעת, הבה נראה כיצד להשתמש בפונקציית SUMIF כדי לסכם ערכים עם קריטריונים שונים.

פונקציית SUMIF עם קריטריוני מספר

אתה יכול לסכם מספרים העומדים בקריטריונים מסוימים בטווח של תאים, על ידי שימוש באחד מאופרטורי ההשוואה הבאים כדי ליצור קריטריונים.

• גדול מ-(>)
• פחות מ-(<)
• גדול או שווה ל-(>=)
• פחות או שווה ל-(<=)
• שווה ל-(=)
• לא שווה ל ()

נניח שיש לך את הגיליון האלקטרוני הבא ואתה מעוניין במכירות הכוללות של 1000 ומעלה.

כך תוכל להזין את הפונקציה SUMIF:

ראשית, בחר את התא שבו תרצה שהפלט של הסכום יופיע (D3). כדי לסכם מספרים ב-B2:B12 שגדולים או שווים ל-1000, הקלד את הנוסחה הזו והקש על 'Enter':

=SUMIF(B2:B12,">=1000",B2:B12)

בנוסחה לדוגמה זו, הארגומנטים range ו-sum_range (B2:B12) זהים, מכיוון שמספרי מכירות וקריטריונים מוחלים על אותו טווח. והכנסנו את המספר לפני אופרטור ההשוואה וסגרנו אותו במרכאות כי הקריטריונים תמיד צריכים להיות מוקפים במרכאות כפולות חוץ מהפניה לתא.

הנוסחה חיפשה מספרים שגדולים או שווים ל-1000 ולאחר מכן צירפה את כל הערכים התואמים והראתה את התוצאה בתא D3.

מכיוון שהארגומנטים range ו-sum_range זהים, אתה יכול להשיג את אותה תוצאה ללא הארגומנטים sum_range בנוסחה, כך:

=SUMIF(B2:B12,">=1000")

לחלופין, אתה יכול לספק את הפניה לתא (D2) שמכילה את המספר במקום את קריטריוני המספר, ולהצטרף לאופרטור ההשוואה עם הפניה לתא זה בארגומנט הקריטריונים:

=SUMIF(B2:B12,">="&D2)

כפי שאתה יכול לראות, אופרטור ההשוואה עדיין מוזן במרכאות כפולות והאופרטור והפניה לתא משורשרים על ידי אמפרסנד (&). ואתה לא צריך לכלול הפניה לתא במרכאות.

הערה: כאשר אתה מתייחס לתא המכיל קריטריונים, הקפד לא להשאיר שום רווח מוביל או נגרר בערך בתא. אם לערך שלך יש רווח מיותר לפני או אחרי הערך בתא המופנה, הנוסחה תחזיר '0' כתוצאה מכך.

אתה יכול גם להשתמש באופרטורים לוגיים אחרים באותה דרך כדי ליצור תנאים בארגומנט הקריטריונים. לדוגמה, כדי לסכם ערכים הנמוכים מ-500:

=SUMIF(B2:B12,"<500")

סכום אם מספרים שווים ל

אם אתה רוצה להוסיף מספרים השווים למספר מסוים, אתה יכול להזין רק את המספר או להזין את המספר עם סימן השוויון בארגומנט הקריטריון.

לדוגמה, כדי לסכם את סכומי המכירות התואמים (עמודה B) עבור כמויות (עמודה ג') שהערכים שלהן שווים ל-20, נסה כל אחת מהנוסחאות הבאות:

=SUMIF(C2:C12,"=20",B2:B12)‎

=SUMIF(C2:C12,"20",B2:B12)‎

=SUMIF(C2:C12,E2,B2:B12)‎

כדי לסכם מספרים בעמודה B עם כמות שאינה שווה ל-20 בעמודה C, נסה את הנוסחה הזו:

=SUMIF(C2:C12,"20",B2:B12)‎

פונקציית SUMIF עם קריטריוני טקסט

אם ברצונך להוסיף מספרים בטווח תאים (עמודה או שורה) התואם לתאים שיש להם טקסט ספציפי, אתה יכול פשוט לכלול את הטקסט הזה או התא שמכיל את הטקסט בארגומנט הקריטריונים של נוסחת SUMIF שלך. שים לב שמחרוזת טקסט צריכה תמיד להיות מוקפת במירכאות כפולות (" ").

לדוגמה, אם אתה רוצה את כמות המכירות הכוללת באזור 'מערב', תוכל להשתמש בנוסחה הבאה:

=SUMIF(C2:C13,"West",B2:B13)‎

בנוסחה זו, הפונקציה SUMIF מחפשת את הערך 'מערב' בטווח תאים C2:C13 ומסכמת את ערך המכירה המתאים בעמודה B. לאחר מכן מציגה את התוצאה בתא E3.

אתה יכול גם להתייחס לתא המכיל טקסט במקום להשתמש בטקסט בארגומנט הקריטריונים:

=SUMIF(C2:C12,E2,B2:B12)‎

כעת, בואו נקבל את סך ההכנסות של כל האזורים מלבד 'מערב'. לשם כך, נשתמש בלא שווה לאופרטור () בנוסחה:

=SUMIF(C2:C12,""&E2,B2:B12)‎

SUMIF עם WildCards

בשיטה לעיל, הפונקציה SUMIF עם קריטריוני טקסט בודקת את הטווח מול הטקסט שצוין המדויק. לאחר מכן, הוא מסכם את מספרי הערך לטקסט מדויק ומתעלם מכל שאר המספרים כולל מחרוזת טקסט מותאמת חלקית. כדי לסכם את המספרים עם מחרוזות טקסט תואמות חלקיות, עליך להתאים את אחד מהתווים הכלליים הבאים לקריטריונים שלך:

• ? (סימן שאלה) משמש כדי להתאים כל תו בודד, בכל מקום במחרוזת הטקסט.
• * (כוכבית) משמשת למציאת מילים תואמות יחד עם כל רצף של תווים.
• ~ (tilde) משמש כדי להתאים טקסטים עם סימן שאלה (?) או תו כוכבית (*).

אנו נציג את הגיליון האלקטרוני לדוגמה עבור מוצרים וכמויותיהם כדי לסכם מספרים עם תווים כלליים:

כוכבית (*) תו כללי

לדוגמה, אם אתה רוצה לסכם את הכמויות של כל מוצרי אפל, השתמש בנוסחה זו:

=SUMIF(A2:A14,"Apple*",B2:B14)‎

נוסחת SUMIF זו מוצאת את כל המוצרים עם המילה "Apple" בתחילתה וכל מספר תווים אחריה (מסומן ב-'*'). ברגע שנמצא ההתאמה, זה מסכם את כַּמוּת מספרים התואמים למחרוזות הטקסט התואמות.

אפשר גם להשתמש במספר תווים כלליים בקריטריונים. ואתה יכול גם להזין תווים כלליים עם הפניות לתאים במקום טקסט ישיר.

לשם כך, התווים הכלליים חייבים להיות מוקפים במרכאות כפולות (" "), ולשרשור אותם עם הפניות לתא:

=SUMIF(A2:A14,"*"&D2&"*",B2:B14)‎

הנוסחה הזו מחברת את הכמויות של כל המוצרים שיש בהם את המילה 'Redmi', לא משנה היכן ממוקמת המילה במחרוזת.

סימן שאלה (?) תו כללי

אתה יכול להשתמש בתו הכללי של סימן השאלה (?) כדי להתאים מחרוזות טקסט עם כל תו בודד.

לדוגמה, אם אתה רוצה למצוא כמויות של כל גרסאות Xiaomi Redmi 9, אתה יכול להשתמש בנוסחה זו:

=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)

הנוסחה לעיל מחפשת מחרוזות טקסט עם המילה "Xiaomi Redmi 9" ואחריה כל תווים בודדים ומסכמת את התווים המתאימים כַּמוּת מספרים.

Tilde (~) תו כללי

אם ברצונך להתאים סימן שאלה (?) או תו כוכבית (*), הכנס את תו הטילדה (~) לפני התו הכללי בחלק התנאי של הנוסחה.

כדי להוסיף את הכמויות בעמודה B עם המחרוזת המתאימה עם סימן כוכבית בסוף, הזן את הנוסחה הבאה:

=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)

כדי להוסיף כמויות בעמודה B שיש להן סימן שאלה (?) בעמודה A באותה שורה, נסה את הנוסחה הבאה:

=SUMIF(A2:A14,"~?",B2:B14)

פונקציית SUMIF עם קריטריוני תאריך

פונקציית SUMIF יכולה גם לעזור לך לסכם ערכים באופן מותנה על סמך קריטריוני תאריך - לדוגמה, מספרים התואמים לתאריך מסוים, או לפני תאריך, או אחרי תאריך. אתה יכול גם להשתמש בכל אחד מאופרטורי ההשוואה עם ערך תאריך כדי ליצור קריטריונים לתאריך לסיכום מספרים.

יש להזין את התאריך בפורמט התאריך הנתמך ב-Google Sheets, או כהפניה לתא המכילה תאריך, או באמצעות פונקציית תאריך כגון DATE() או TODAY().

אנו נשתמש בגיליון אלקטרוני לדוגמה זה כדי להראות לך כיצד פועלת פונקציית SUMIF עם קריטריוני תאריך:

נניח שברצונך לסכם את סכומי המכירות שקרו ב-29 בנובמבר 2019 או לפני (<=) במערך הנתונים שלמעלה, תוכל להוסיף את מספרי המכירות הללו באמצעות פונקציית SUMIF באחת מהדרכים הבאות:

=SUMIF(C2:C13,"<=29 בנובמבר 2019",B2:B13)

הנוסחה לעיל בודקת כל תא מ-C2 עד C13 ומתאימה רק לאותם תאים המכילים תאריכים ב-29 בנובמבר 2019 או לפני כן (29/11/2019). ולאחר מכן מסכם את כמות המכירות התואמת לאותם תאים תואמים מטווח התאים B2:B13 ומציג את התוצאה בתאים E3.

ניתן לספק את התאריך לנוסחה בכל פורמט שמזוהה על ידי Google Sheets, כמו '29 בנובמבר 2019', '29 בנובמבר 2019' או '29/11/2019' וכו'. יש לזכור את ערך התאריך והאופרטור חייב תמיד להיות מוקף במרכאות כפולות.

אתה יכול גם להשתמש בפונקציה DATE() בקריטריונים במקום ערך תאריך ישיר:

=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)‎

לחלופין, אתה יכול להשתמש בהפניה לתא במקום בתאריך בחלק הקריטריונים של הנוסחה:

=SUMIF(C2:C13,"<="&E2,B2:B13)‎

אם ברצונך להוסיף את סכומי המכירות יחד על סמך התאריך של היום, תוכל להשתמש בפונקציה TODAY() בארגומנט הקריטריונים.

לדוגמה, כדי לסכם את כל סכומי המכירות עבור התאריך של היום, השתמש בנוסחה זו:

=SUMIF(C2:C13,TODAY(),B2:B13)‎

פונקציית SUMIF עם תאים ריקים או לא ריקים

לפעמים, ייתכן שיהיה עליך לסכם את המספרים בטווח של תאים עם תאים ריקים או לא ריקים באותה שורה. במקרים כאלה, אתה יכול להשתמש בפונקציה SUMIF כדי לסכם ערכים על סמך קריטריונים שבהם התאים ריקים או לא.

סכום אם ריק

ישנם שני קריטריונים ב-Google Sheets למציאת תאים ריקים: "" או "=".

לדוגמה, אם ברצונך לסכם את כל כמות המכירות המכילה מחרוזות באורך אפס (נראה חזותית ריקה) בעמודה C, השתמש במרכאות כפולות ללא רווח ביניהן בנוסחה:

=SUMIF(C2:C13,"",B2:B13)‎

כדי לסכם את כל סכום המכירות בעמודה B עם תאים ריקים מלאים בעמודה C, כלול "=" כקריטריונים:

=SUMIF(C2:C13,"=",B2:B13)‎

סכום אם לא ריק:

אם ברצונך לסכם תאים המכילים ערך כלשהו (לא ריקים), תוכל להשתמש ב-"" כקריטריון בנוסחה:

לדוגמה, כדי לקבל את כמות המכירות הכוללת עם כל תאריכים, השתמש בנוסחה זו:

=SUMIF(C2:C13,"",B2:B13)‎

SUMIF מבוסס על קריטריונים מרובים עם OR Logic

כפי שראינו עד כה פונקציית SUMIF נועדה לסכם מספרים על סמך קריטריון בודד בלבד, אך ניתן לסכם ערכים על סמך מספר קריטריונים עם הפונקציה SUMIF ב-Google Sheets. ניתן לעשות זאת על ידי חיבור של יותר מפונקציית SUMIF אחת בנוסחה אחת עם לוגיקה OR.

לדוגמה, אם ברצונך לסכם את סכום המכירות באזור 'מערב' או באזור 'דרום' (או לוגיקה) בטווח שצוין (B2:B13), השתמש בנוסחה זו:

=SUMIF(C2:C13,"West",B2:B13)+SUMIF(C2:C13,"South",B2:B13)‎

נוסחה זו מסכמת תאים כאשר לפחות אחד מהתנאים הוא TRUE. מכאן שזה ידוע בשם 'היגיון או'. זה גם יסכם ערכים כאשר כל התנאים מתקיימים.

החלק הראשון של הנוסחה בודק את הטווח C2:C13 עבור הטקסט 'מערב' ומסכם את הערכים בטווח B2:B13 כאשר ההתאמה מתקיימת. החלק השניות של הבדיקות בודק את ערך הטקסט 'דרום' באותו טווח C2:C13 ולאחר מכן מסכם ערכים עם הטקסט התואם באותו sum_range B2:B13. אז שני הסכומים מתווספים יחד ומוצגים בתא E3.

במקרים שרק קריטריון אחד מתקיים, הוא יחזיר רק את ערך הסכום הזה.

אתה יכול גם להשתמש במספר קריטריונים במקום רק אחד או שניים. ואם אתה משתמש במספר קריטריונים, עדיף להשתמש בהפניה לתא כקריטריון במקום לכתוב את הערך הישיר בנוסחה.

=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)

SUMIF עם לוגיקה OR מוסיפה ערכים כאשר מתקיים לפחות אחד מהקריטריונים שצוינו, אך אם ברצונך לסכם ערכים רק כאשר כל התנאים שצוינו מתקיימים, עליך להשתמש בפונקציית האח החדשה SUMIFS() שלו.

פונקציית SUMIFS ב-Google Sheets (קריטריונים מרובים)

כאשר אתה משתמש בפונקציה SUMIF כדי לסכם ערכים על סמך מספר קריטריונים, הנוסחה עשויה להיות ארוכה ומסובכת מדי, ואתה נוטה לעשות טעויות. חוץ מזה SUMIF יאפשר לך לסכם ערכים רק בטווח בודד וכאשר כל אחד מהתנאים הוא TRUE. שם נכנסת לתמונה פונקציית SUMIFS.

הפונקציה SUMIFS עוזרת לך לסכם ערכים על סמך מספר קריטריונים התאמה בטווח אחד או יותר. וזה עובד על AND logic, כלומר הוא יכול לסכם ערכים רק כאשר כל התנאים הנתונים מתקיימים. גם אם תנאי אחד הוא שקרי, הוא יחזיר '0' כתוצאה מכך.

תחביר וארגומנטים של פונקציות SUMIFS

התחביר של פונקציית SUMIFS הוא כדלקמן:

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, ...], [criteria2, ...])

איפה,

• טווח_סכום - טווח התאים המכילים את הערכים שברצונך לסכם כאשר כל התנאים מתקיימים.
• טווח_קריטריונים1 – זהו טווח התאים שבו אתה בודק קריטריונים1.
• קריטריונים1 - זה התנאי שאתה צריך לבדוק מול criteria_range1.
• criteria_range2, criterion2, …– הטווחים והקריטריונים הנוספים שיש להעריך. ואתה יכול להוסיף עוד טווחים ותנאים לנוסחה.

נשתמש במערך הנתונים בצילום המסך הבא כדי להדגים כיצד פועלת הפונקציה SUMIFS עם קריטריונים שונים.

SUMIFS עם תנאי טקסט

ניתן לסכם ערכים על סמך שני קריטריונים שונים של טקסט בטווחים שונים. לדוגמה, נניח שאתה רוצה לברר את סכום המכירות הכולל של פריט האוהל שנמסר. לשם כך, השתמש בנוסחה זו:

=SUMIFS(D2:D13,A2:A13,"Tent",C2:C13,"Delivered")

בנוסחה זו, יש לנו שני קריטריונים: "אוהל" ו"נמסר". הפונקציה SUMIFS בודקת את הפריט 'Tent' (criteria1) בטווח A2:A13 (criteria_range1) ובודקת את המצב 'Delivered' (criteria2) בטווח C2:C13 (criteria_range2). כאשר שני התנאים מתקיימים, הוא מסכם את הערך המתאים בטווח התאים D2:D13 (sum_range).

SUMIFS עם קריטריוני מספר ואופרטורים לוגיים

אתה יכול להשתמש באופרטורים מותנים כדי ליצור תנאים עם מספרים עבור הפונקציה SUMIFS.

כדי למצוא את סך המכירות של יותר מ-5 כמויות של פריט כלשהו במדינת קליפורניה (CA), השתמש בנוסחה זו:

=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")

לנוסחה זו יש שני תנאים: ">5" ו-"CA".

נוסחה זו בודקת כמויות (כמות) גדולות מ-5 בטווח D2:D13 ובודקת את המצב 'CA' בטווח B2:B13. וכאשר שני התנאים מתקיימים (כלומר יש באותה שורה), זה מסכם את הסכום ב-E2:E13.

SUMIFS עם קריטריוני תאריך

פונקציית SUMIFS מאפשרת לך גם לבדוק תנאים מרובים באותו טווח כמו גם טווחים שונים.

נניח שברצונך לבדוק את סכום המכירות הכולל של הפריטים שנמסרו לאחר 31/5/2021 ולפני תאריך 10/6/2021, ולאחר מכן השתמש בנוסחה זו:

=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)

לנוסחה שלעיל יש שלושה תנאים: 31/5/2021, 10/5/2021, ומסירה. במקום להשתמש בערכי תאריך וטקסט ישירים, התייחסנו לתאים המכילים קריטריונים אלה.

הנוסחה בודקת תאריכים לאחר 31/5/2021 (G1) ותאריכים לפני 10/6/2021 (G2) באותו טווח D2:D13, ובודקת את הסטטוס 'מסירה' בין שני התאריכים הללו. לאחר מכן, מסכם את הסכום הקשור בטווח E2:E13.

SUMIFS עם תאים ריקים ולא ריקים

לפעמים, ייתכן שתרצה למצוא את סכום הערכים כאשר תא מתאים ריק או לא. כדי לעשות זאת, אתה יכול להשתמש באחד משלושת הקריטריונים שדיברנו עליהם קודם: "=", "" ו-".

לדוגמה, אם ברצונך לסכם רק את הכמות של פריטי 'אוהל' שעדיין לא אושר להם תאריך המשלוח (תאים ריקים), תוכל להשתמש בקריטריונים של "=":

=SUMIFS(D2:D13,A2:A13,"Tent",C2:C13,"=")

הנוסחה מחפשת את הפריט 'אוהל' (קריטריונים1) בעמודה A עם תאים ריקים מתאימים (קריטריונים2) בעמודה C ולאחר מכן מסכמת את הכמות המקבילה בעמודה D. ה-"=" מייצג תא ריק לחלוטין.

כדי למצוא את הסכום של פריטי 'אוהל' שעבורם אושר תאריך המשלוח (לא תאים ריקים), השתמש ב-"" כקריטריון:

=SUMIFS(D2:D13,A2:A13,"Tent",C2:C13,"")

פשוט החלפנו את "=" ל"" בנוסחה הזו. הוא מוצא את הסכום של פריטי אוהל עם תאים לא ריקים בעמודה C.

SUMIFS עם OR Logic

מכיוון שהפונקציה SUMIFS פועלת על לוגיקה AND, היא מסתכמת רק כאשר כל התנאים מתקיימים. אבל מה אם אתה רוצה לסכם ערך על סמך מספר קריטריונים כאשר אחד מהקריטריונים מתקיים. החוכמה היא להשתמש במספר פונקציות SUMIFS.

לדוגמה, אם אתה רוצה להוסיף את סכום המכירות עבור 'מתלה אופניים' או 'תרמיל' כשהסטטוס שלהם הוא 'מוזמן', נסה את הנוסחה הזו:

=SUMIFS(D2:D13,A2:A13,"מתלה לאופניים",C2:C13,"Ordered") +SUMIFS(D2:D13,A2:A13,"Backpack",C2:C13,"Ordered")

הפונקציה SUMIFS הראשונה בודקת שני קריטריונים "מתלה אופניים" ו"מוזמן" ומסכמת את ערכי הכמות בעמודה D. לאחר מכן, ה-SUMIFS השנייה בודקת שני קריטריונים "תרמיל" ו"מוזמן" ומסכמת את ערכי הכמות בעמודה D. ואז , שני הסכומים מתווספים יחד ומוצגים ב-F3. במילים פשוטות, נוסחה זו מסתכמת כאשר מזמינים 'מתלה אופניים' או 'תרמיל'.

זה כל מה שאתה צריך לדעת על פונקציית SUMIF ו-SUMIFS ב-Google Sheets.