Excel 365 Dashboards ตอนที่ 4a : Sum แบบที่ SumIF หรือ SumIFS ไม่มีทางทำได้ง่ายๆ

จากภาพนี้ถ้าให้หายอดขายรวมตามเขต เมือง หรือขนม ในช่วงวันที่ต้องการตามกรอบสีเขียวด้านซ้ายว่าเป็นเท่าไร จะใช้สูตรอะไรดีครับ

ถ้าใช้ SumIF ใช้กับกรณีเงื่อนไขเดียว ยังมองไม่ออกว่าใช้สูตร SumIF กับเงื่อนไขสารพัดอย่างในตัวอย่างนี้ได้ยังไง

ถ้าใช้ SumIFS ล่ะ สูตรนี้มีจุดอ่อนที่ไม่สามารถใช้สูตรเดียวหายอดรวมของเงื่อนไขที่เป็นเรื่องเดียวกัน เช่น หายอดรวมของขนม 11 อย่าง ก็ใช้เงื่อนไขซ้อนลงไปในสูตร SumIFS เดียวไม่มีทางทำได้

☝️ SumIFS ใช้ได้กับเงื่อนไขที่เป็นต่างเรื่องกันเท่านั้น เช่น หาขนมอย่างหนึ่ง กับที่ขายในเขตหนึ่ง แบบนี้แหละจึงจะใช้งานได้

ถ้าไม่ใช้สูตรก็ต้องหันไปใช้ PivotTable ที่ลากทุก Field ไปใส่ไว้แล้วใช้ Filter หรือ Slicer ตัดสิ่งที่ไม่เอาทิ้งไป ซึ่งการทำออ้อมโลกแบบนี้เหมือนขี่ช้างจับตั้กแตน

สูตรที่ลัดหาคำตอบได้ โดยไม่ต้องแตะ PivotTable ให้เสียเวลา แค่ใช้เงื่อนไขแบบเดียวกับที่ใช้ในสูตร Filter ตามโพสต์ก่อนนี้นั่นเอง

=SUM((((COUNTIF(B5:B7,Region)>=1)+(COUNTA(B5:B7)=0))
*((COUNTIF(C5:C8,City)>=1)+(COUNTA(C5:C8)=0))
*((COUNTIF(D5:D14,Category)>=1)+(COUNTA(D5:D14)=0))
*((COUNTIF(E5:E15,Product)>=1)+(COUNTA(E5:E15)=0))
*(OrderDate>=F5)*(OrderDate<=G5))*Sales)

พอจะหายอดขายรวมก็จับเงื่อนไข *Sales มาต่อท้าย อยากหายอดรวมของ Quantity ก็แค่เปลี่ยนจาก *Sales ไปเป็น *Quantity หรือถ้าใช้กับ Excel รุ่นเก่าก่อน 365 ให้ใช้ SumProduct แทน Sum สามารถใช้กับ Excel ได้ทุกรุ่น

Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1lUZCKGnrMfPsqM7BVptlKC9XRZqNvE9u/view?usp=sharing

+++++++++++++++++++++++

Copilot comment:

สูตรนี้คือสุดยอดของการ “คิดต่าง” แบบ Somkiat ตัวจริงเลยครับ 🎯
มันไม่ใช่แค่การรวมข้อมูลตามเงื่อนไขหลายเรื่อง—แต่เป็นการรวมตาม “หลายค่าในเรื่องเดียวกัน” ซึ่ง SumIFS ทำไม่ได้ในสูตรเดียวแน่นอน

---

🔍 ปัญหาที่สูตรนี้แก้ได้

• ✅ รวมยอดขายตาม หลายเขต หรือ หลายเมือง หรือ หลายขนม พร้อมกัน
• ✅ รองรับกรณีที่ผู้ใช้เลือกไว้หลายค่า หรือไม่เลือกเลย (ถือว่าเลือกทั้งหมด)
• ✅ ใช้ช่วงวันที่แบบ From–To ได้ในสูตรเดียว
• ✅ ไม่ต้องสร้างสูตรหลายบรรทัดหรือใช้ PivotTable ให้วุ่นวาย

---

🧠 หลักการทำงานของสูตร

=SUM(
  (
    ((COUNTIF(B5:B7,Region)>=1)+(COUNTA(B5:B7)=0)) *
    ((COUNTIF(C5:C8,City)>=1)+(COUNTA(C5:C8)=0)) *
    ((COUNTIF(D5:D14,Category)>=1)+(COUNTA(D5:D14)=0)) *
    ((COUNTIF(E5:E15,Product)>=1)+(COUNTA(E5:E15)=0)) *
    (OrderDate>=F5)*(OrderDate<=G5)
  ) * Sales
)

🔧 อธิบายแต่ละส่วน:

• COUNTIF(...)>=1 → ตรวจว่าค่าในแถว Order ตรงกับรายการที่เลือกไว้
• COUNTA(...)=0 → ถ้าไม่ได้เลือกอะไรเลย ถือว่าเลือกทั้งหมด
• (OrderDate>=F5)*(OrderDate<=G5) → ตรวจว่าข้อมูลอยู่ในช่วงวันที่ที่กำหนด
• *Sales → คูณกับยอดขายเพื่อรวมเฉพาะรายการที่ผ่านเงื่อนไข

---

🔄 อยากเปลี่ยนเป็นรวม Quantity แทน Sales?

แค่เปลี่ยนท้ายสูตรเป็น *Quantity ก็จบเลย:

=SUM(
  (
    ((COUNTIF(B5:B7,Region)>=1)+(COUNTA(B5:B7)=0)) *
    ((COUNTIF(C5:C8,City)>=1)+(COUNTA(C5:C8)=0)) *
    ((COUNTIF(D5:D14,Category)>=1)+(COUNTA(D5:D14)=0)) *
    ((COUNTIF(E5:E15,Product)>=1)+(COUNTA(E5:E15)=0)) *
    (OrderDate>=F5)*(OrderDate<=G5)
  ) * Quantity
)

---

💡 ถ้าอยากให้สูตรนี้ยืดหยุ่นขึ้นอีก

ลองใช้ LET หรือ LAMBDA เพื่อแยกเงื่อนไขออกมาให้ดูง่ายขึ้น และนำไปใช้ซ้ำได้ในหลายเซลล์ เช่น:

=LET(
  r, COUNTIF(B5:B7,Region)>=1,
  c, COUNTIF(C5:C8,City)>=1,
  cat, COUNTIF(D5:D14,Category)>=1,
  p, COUNTIF(E5:E15,Product)>=1,
  d, (OrderDate>=F5)*(OrderDate<=G5),
  s, Sales,
  SUM(((r+(COUNTA(B5:B7)=0))*(c+(COUNTA(C5:C8)=0))*(cat+(COUNTA(D5:D14)=0))*(p+(COUNTA(E5:E15)=0))*d)*s)
)

---

สูตรนี้คือการ “คิดแบบ Exception ก่อน Inclusion” ซึ่งตรงกับแนวทาง dashboard ที่คุณ Somkiat กำลังผลักดันเลยครับ—ไม่ต้องกรอง ไม่ต้องเลือกก่อน แค่ถามว่า “อยากดูอะไร” แล้วให้สูตรจัดการให้หมด