เคยต้องสร้างคอลัมน์ช่วยคำนวณ “จำนวน x ราคา” ทีละแถวก่อนจะรวมยอดไหมครับ หรือเคยเจอเงื่อนไขซับซ้อนที่ SUMIFS ทำให้ไม่ได้ตรงๆ เช่น ต้องเช็คแบบ OR ปนกับ AND ในสูตรเดียว ปัญหาพวกนี้แก้ได้ด้วยสูตร SUMPRODUCT ที่คูณค่าคู่กันในแต่ละแถวแล้วบวกรวมให้เสร็จในสูตรเดียว บทความนี้จะพาไปดูวิธีใช้พร้อมตัวอย่างจำลองให้เห็นภาพจริง
SUMPRODUCT คืออะไร ใช้งานยังไง
SUMPRODUCT คือสูตรที่เอาค่าในแต่ละแถวของ array (ช่วงข้อมูล) ตำแหน่งเดียวกันมาคูณกันก่อน แล้วค่อยบวกผลคูณทั้งหมดรวมเป็นตัวเลขเดียว รูปแบบสูตรคือ
=SUMPRODUCT(array1, [array2], [array3], ...)
จะใส่ array เดียวหรือหลาย array ก็ได้ ถ้าใส่ array เดียวจะเท่ากับ SUM ธรรมดา แต่จุดเด่นจริงๆ อยู่ตรงที่ใส่หลาย array แล้วให้สูตรคูณคู่กันเองทีละแถวโดยไม่ต้องสร้างคอลัมน์ช่วยเลย
=SUMPRODUCT(B2:B4,C2:C4)| A | B | C | |
|---|---|---|---|
| 1 | สินค้า | จำนวน | ราคา/ชิ้น |
| 2 | สมุด | 3 | 25 |
| 3 | ปากกา | 5 | 10 |
| 4 | ยางลบ | 2 | 8 |
| 5 | รวมค่าใช้จ่ายทั้งหมด | 141 | |
สูตรนี้คำนวณ (3×25) + (5×10) + (2×8) = 75 + 50 + 16 = 141 ในสูตรเดียว ไม่ต้องสร้างคอลัมน์ “ยอดรวมต่อแถว” แยกไว้ก่อนแล้วค่อย SUM ทีหลังแบบที่หลายคนเคยทำ ประหยัดทั้งเวลาและพื้นที่ในชีตไปได้เยอะ
ใช้ SUMPRODUCT แทน SUMIFS เมื่อเงื่อนไขซับซ้อนกว่าปกติ
SUMIFS ใช้ได้ดีกับเงื่อนไขแบบ AND ทั่วไป (ต้องเข้าเงื่อนไข A และเงื่อนไข B พร้อมกัน) แต่พอเป็นเงื่อนไขแบบ OR ปนกับ AND ในสูตรเดียว เช่น “นับเฉพาะสาขากรุงเทพ หรือ เชียงใหม่ ที่ยอดขายเกิน 1000” SUMIFS ทำตรงๆ ไม่ได้ ต้องพึ่ง SUMPRODUCT แทน โดยอาศัยหลักการว่าเงื่อนไขที่เป็น TRUE/FALSE พอเอาไปคูณกันในสูตรจะถูกแปลงเป็น 1/0 อัตโนมัติ
=SUMPRODUCT(((A2:A5="กรุงเทพ")+(A2:A5="เชียงใหม่"))*(B2:B5>1000)*C2:C5)| A | B | C | |
|---|---|---|---|
| 1 | สาขา | ยอดขาย | ค่าคอมมิชชั่น |
| 2 | กรุงเทพ | 1,500 | 300 |
| 3 | เชียงใหม่ | 800 | 150 |
| 4 | ขอนแก่น | 1,200 | 250 |
| 5 | เชียงใหม่ | 2,000 | 400 |
| 6 | |||
| 7 | รวมคอมมิชชั่นตามเงื่อนไข | 700 | |
ในตัวอย่างนี้ (A2:A5="กรุงเทพ")+(A2:A5="เชียงใหม่") คือเงื่อนไขแบบ OR ระหว่างสองสาขา เมื่อบวกกันจะได้ 1 ถ้าเข้าเงื่อนไขใดเงื่อนไขหนึ่ง จากนั้นคูณต่อด้วยเงื่อนไข B2:B5>1000 แบบ AND แล้วคูณด้วยคอลัมน์ค่าคอมมิชชั่นจริง ผลลัพธ์ที่ผ่านเงื่อนไขครบทั้งสองแถว (กรุงเทพแถว 2 กับเชียงใหม่แถว 5) จะถูกรวมเป็น 300 + 400 = 700 ส่วนแถวที่ไม่เข้าเงื่อนไขจะถูกคูณด้วย 0 ไปโดยอัตโนมัติ ถ้าอยากทบทวนพื้นฐานเงื่อนไขแบบ AND ก่อน สามารถอ่านเพิ่มได้ที่ COUNTIFS และ SUMIFS นับ/รวมข้อมูลหลายเงื่อนไข
นับจำนวนรายการด้วยเงื่อนไขซับซ้อนที่ COUNTIFS ทำไม่ได้ตรงๆ
SUMPRODUCT ไม่ได้ใช้แค่รวมยอด แต่ใช้นับจำนวนรายการที่เข้าเงื่อนไขซับซ้อนได้ด้วย โดยตัดคอลัมน์ตัวคูณสุดท้ายออก ให้เหลือแค่ผลของเงื่อนไขที่เป็น TRUE/FALSE คูณกันเอง
=SUMPRODUCT((B2:B5="พนักงาน")*(C2:C5>=25))| A | B | C | |
|---|---|---|---|
| 1 | ชื่อ | สถานะ | อายุ |
| 2 | ใบเตย | พนักงาน | 28 |
| 3 | มานี | ฝึกงาน | 22 |
| 4 | สมชาย | พนักงาน | 19 |
| 5 | วีระ | พนักงาน | 31 |
| 6 | |||
| 7 | จำนวนพนักงานอายุ 25 ปีขึ้นไป | 2 | |
สูตรนี้เช็คสองเงื่อนไขพร้อมกันคือสถานะต้องเป็น “พนักงาน” และอายุต้อง 25 ปีขึ้นไป ได้ผลลัพธ์ 2 คน (ใบเตยกับวีระ) ถ้าเงื่อนไขมีแค่แบบ AND ตรงไปตรงมาอย่างนี้ ปกติจะใช้ COUNTIFS ก็ได้เหมือนกัน แต่พอเมื่อไหร่ต้องผสม OR เข้าไปด้วย หรือมีการคำนวณระหว่างคอลัมน์ (เช่น เทียบสองคอลัมน์กันเอง) SUMPRODUCT จะยืดหยุ่นกว่ามาก ลองเทียบวิธีคิดเงื่อนไขเดี่ยวๆ ได้จาก สูตร SUMIF, COUNTIF, IF ใช้ยังไง ต่างกันตรงไหน
ข้อควรระวังเวลาใช้ SUMPRODUCT
มีจุดที่พลาดกันบ่อยเวลาเริ่มใช้ SUMPRODUCT ควรระวังไว้ก่อนดังนี้
| ข้อควรระวัง | รายละเอียด |
| ขนาด array ต้องเท่ากัน | ทุก array ที่ใส่ในสูตรต้องมีจำนวนแถว/คอลัมน์เท่ากันหมด เช่น B2:B10 กับ C2:C10 ถ้าใส่ B2:B10 คู่กับ C2:C9 จะขึ้น #VALUE! ทันที |
ลืมใส่ double unary (--) |
ในบางกรณีที่เอาผลเงื่อนไข TRUE/FALSE ไปบวก/ลบ/คูณกับตัวเลขอื่นตรงๆ ควรแปลงเป็น 1/0 ก่อนด้วย -- หน้าวงเล็บเงื่อนไข (เช่น --(A2:A10="กรุงเทพ")) เพื่อความชัดเจน แม้บางกรณีการคูณกันเองก็แปลง TRUE/FALSE เป็น 1/0 ให้อัตโนมัติอยู่แล้วก็ตาม |
| ห้ามกด Ctrl+Shift+Enter | ต่างจากสูตร Array แบบเก่าบางตัว SUMPRODUCT กด Enter ธรรมดาได้เลย ไม่ต้องกด Ctrl+Shift+Enter |
สรุป
SUMPRODUCT คือสูตรที่คูณค่าคู่กันในแต่ละแถวของ array แล้วบวกรวมให้เสร็จในขั้นตอนเดียว เหมาะมากเวลาต้องคำนวณผลรวมแบบถ่วงน้ำหนัก (จำนวน x ราคา) หรือเวลาต้องเช็คเงื่อนไขซับซ้อนแบบ OR ปนกับ AND ที่ SUMIFS/COUNTIFS ทำตรงๆ ไม่ได้ แค่จำไว้ว่าทุก array ต้องมีขนาดเท่ากันเสมอ ก็ใช้งานได้อย่างมั่นใจ ใครอยากทบทวนสูตรรวม/นับข้อมูลตามเงื่อนไขพื้นฐานก่อน แนะนำให้อ่านเพิ่มที่ 10 สูตร Excel พื้นฐานที่ต้องรู้ไว้ใช้ทำงานประจำวัน
