הפונקציות SUMIF ו-COUNTIF מוגבלות לתנאי אחד. התחביר של שתי הפונקציות דומה
=SUMIF(range,criteria,[sum_range])
=COUNTIF(range,criteria)
הארגומנט [sum_range] ב-SUMIF אופציונאלי.
נניח טבלה עם נתוני מכירות של סניפים מחולקים לפי אזורים
כדי לחשב כמה סניפים יש בכל אזור (תא G4) נשתמש בנוסחה
=COUNTIF(C3:C22,G3)
וכדי לחשב את סה"כ מכירות של האזור נשתמש ב:
=SUMIF(C3:C22,G3,D3:D22)
בתא G3 יצרנו רשימה נפתחת של אזורים בעזרת אימות נתונים, כך שלא נצטרך לערוך את הנוסחאות מחדש לחישוב נתוני כל אזור.
כאמור, שתי הפונקציות מוגבלות לקריטריון אחד. הפתרון לחישוב עם יותר מקריטריון אחד תלוי באיזו גרסה של Excel משתמשים.
קריטריונים מרובים, טווח אחיד
כמה סניפים מכרו בין 150,000 ₪ לבין 200,000 ₪ ?
פתרון עם SUMPRODUCT
=SUMPRODUCT((D3:D22>=150000)*(D3:D22<=200000))
SUMPRODUCT יוצרת שני מערכים של ערכים FALSE ו-TRUE, מכפילה בניהם ומסכמת את תוצאות ההכפלה. הטבלה להלן מדגימה את הפעולה של SUMPRODUCT
פתרון עם COUNTIFS (רק Excel 2007/10)
=COUNTIFS(D3:D22,">=150000",D3:D22,"<=200000")
בכמה מכרו את הסניפים הללו?
פתרון עם SUMPRODUCT
=SUMPRODUCT((D3:D22>=150000)*(D3:D22<=200000)*D3:D22)
הנוסחה זהה לנוסחת הספירה עם תוספת טווח המכירות.
פתרון עם SUMIFS (רק Excel 2007/10)
=SUMIFS(D3:D22,D3:D22,">=150000",D3:D22,"<=200000")
קריטריונים מרובים, טווחים מרובים
כמה סניפים באזור הצפון מכרו מעל 150,000 ₪?
פתרון עם SUMPRODUCT
=SUMPRODUCT((C3:C22="צפון")*(D3:D22>150000))
פתרון עם COUNTIFS
=COUNTIFS(C3:C22,"צפון",D3:D22,">150000")
בכמה מכרו הסניפים הללו?
פתרון עם SUMPRODUCT
=SUMPRODUCT((C3:C22="צפון")*(D3:D22>150000)*D3:D22)
פתרון עם SUMIFS (רק Excel 2007/10)
=SUMIFS(D3:D22,C3:C22,"צפון",D3:D22,">150000")
