SumProduct เป็นสูตรที่ผมพบจุดอ่อนว่า ในบางครั้งหากสร้างสูตรลิงค์หายอดรวมข้ามแฟ้ม จะหายอดรวมไม่ได้หากแฟ้มต้นทางไม่ได้เปิดพร้อมกับแฟ้มปลายทาง

คราวนี้พบจุดอ่อนอีกอย่างหนึ่ง (หรือผมพลาดตรงไหนก็ไม่ทราบ) ทำไมสูตร SumProduct จึงทำงานไม่ได้ตามต้องการ

ตามตัวอย่างแนบนี้ โดยทั่วไปสูตรหายอดรวมของชื่อที่กำหนด จะใช้กันตามนี้ใช่ไหมครับ
=SUMPRODUCT((Name=$F$3)*Amount)
=SUMPRODUCT(--(Name=$F$3),Amount)

โดยทั่วไป ในกรณีที่ไม่ได้ใส่ชื่อไว้ในเซลล์ F3 ย่อมหายอดรวมเท่ากับ 0

แต่คราวนี้ ผมต้องการปรับสูตรให้ใช้เงื่อนไขใหม่ว่า
ถ้าไม่ได้ใส่ชื่อไว้ในเซลล์ F3
ให้หายอดรวมทั้งหมดมาใช้แทน

พบว่าสูตร SumProduct ต่อไปนี้หาค่าไม่ได้ เมื่อมีชื่อกรอกไว้ในเซลล์ F3 แต่จะหายอดรวมทั้งหมดได้ถ้าลบชื่อใน F3 ทิ้ง
=SUMPRODUCT(IF(ISBLANK($F$3),1,(Name=$F$3))*Amount)
=SUMPRODUCT(IF(ISBLANK($F$3),1,(Name=$F$3)),Amount)
=SUMPRODUCT(IF($F$3=0,1,(Name=$F$3))*Amount)
=SUMPRODUCT(1*IF(ISBLANK($F$3),1,(Name=$F$3)),Amount)

ส่วนสูตร SumIF Array ต่อไปนี้จะทำงานได้ถูกต้องเสมอ คือ เมื่อกำหนดชื่อในเซลล์ F3 ก็จะหายอดของเฉพาะชื่อนั้น
หรือเมื่อลบชื่อทิ้ง ก็จะหายอดรวมทั้งหมดมาให้ครับ
{=SUM(IF(ISBLANK($F$3),1,(Name=$F$3))*Amount)}
{=SUM(IF($F$3=0,1,(Name=$F$3))*Amount)}

ฝากพวกเราช่วยกันดูสูตรให้ด้วยครับว่า แทนที่จะสร้างสูตร SumProduct แบบที่ผมทำมา
จะใช้สูตร SumProduct แบบใด เพื่อคำนวณหายอดรวมของชื่อที่กำหนด
ต่อมาถ้าไม่ได้ใส่ชื่อไว้ในเซลล์ F3
ให้หายอดรวมทั้งหมดมาใช้แทน

ปล
ประโยชน์ของสูตรแบบนี้คือ
ถ้ามีเงื่อนไขมากกว่าหนึ่งเงื่อนไขผสมกันไว้ในสูตร เราสามารถเลือกให้สูตรคำนวณเฉพาะเงื่อนไขที่มีค่าเท่านั้น ส่วนเงื่อนไขอื่นที่ไม่ได้ใส่ข้อมูลไว้ก็จะถือว่า ไม่ได้ใช้งาน แบบสูตรนี้ครับ

{=SUM(IF(ISBLANK(cell1),1,(Key1Range=cell1)) * IF(ISBLANK(cell2),1,(Key2Range=cell2)) * IF(ISBLANK(cell3),1,(Key3Range=cell3))*Amount)}