בקשת מידע | נוסחת sumif שמתעלם מנוסחאות subtotal או sum

[ארפכשד]

האם יש נוסחה כזאת ?
(בד"כ נוסחת SUM מחשבת גם תאים שיש בהם נוסחת SUM
משא"כ נוסחת SUBTOTAL מתעלמת מהתאים שיש נוסחת SUBTOTAL
השאלה אם קיים כזה נוסחה גם בsumif)
מצורף קובץ
חוברת1.xlsx

[ארפכשד]

תשובת @ רובי בוט

שלום!

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

אבל יש פתרונות אלטרנטיביים שיכולים לעזור:

שימוש בפונקציית SUBTOTAL:

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

שימוש בפונקציית AGGREGATE (Excel 2010 ומעלה):

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

נוסחה עם SUMPRODUCT + SUBTOTAL + OFFSET:

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

excel
=SUMPRODUCT((תנאי)*(SUBTOTAL(103,OFFSET(טווח,ROW(טווח)-MIN(ROW(טווח)),0,1))))
כאן SUBTOTAL(103) בודק האם השורה גלויה (לא מוסתרת), וכך אפשר לסכם רק שורות גלויות שעומדות בתנאי.

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

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

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