หน้าแรก › บทความ › Excel
Excel

สูตร SUMPRODUCT ใน Excel คูณแล้วบวกในสูตรเดียว ใช้แทน SUMIFS ก็ได้

เผยแพร่ 13 ส.ค. 69

เคยต้องสร้างคอลัมน์ช่วยคำนวณ “จำนวน x ราคา” ทีละแถวก่อนจะรวมยอดไหมครับ หรือเคยเจอเงื่อนไขซับซ้อนที่ SUMIFS ทำให้ไม่ได้ตรงๆ เช่น ต้องเช็คแบบ OR ปนกับ AND ในสูตรเดียว ปัญหาพวกนี้แก้ได้ด้วยสูตร SUMPRODUCT ที่คูณค่าคู่กันในแต่ละแถวแล้วบวกรวมให้เสร็จในสูตรเดียว บทความนี้จะพาไปดูวิธีใช้พร้อมตัวอย่างจำลองให้เห็นภาพจริง

🎬 ดูวิดีโอสอน (1 นาที 33 วินาที) บน Excel จริง หรืออ่านขั้นตอนละเอียดด้านล่างได้เลย

SUMPRODUCT คืออะไร ใช้งานยังไง

SUMPRODUCT คือสูตรที่เอาค่าในแต่ละแถวของ array (ช่วงข้อมูล) ตำแหน่งเดียวกันมาคูณกันก่อน แล้วค่อยบวกผลคูณทั้งหมดรวมเป็นตัวเลขเดียว รูปแบบสูตรคือ

=SUMPRODUCT(array1, [array2], [array3], ...)

จะใส่ array เดียวหรือหลาย array ก็ได้ ถ้าใส่ array เดียวจะเท่ากับ SUM ธรรมดา แต่จุดเด่นจริงๆ อยู่ตรงที่ใส่หลาย array แล้วให้สูตรคูณคู่กันเองทีละแถวโดยไม่ต้องสร้างคอลัมน์ช่วยเลย

ตัวอย่างสูตร =SUMPRODUCT(B2:B4,C2:C4) ใน Excel (ภาพจากหน้าจอจริง)
ภาพจากหน้าจอ Excel จริง (ตัวอย่างข้อมูลในวิดีโอ)

สูตรนี้คำนวณ (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 ใน Excel (ภาพจากหน้าจอจริง)” width=”1882″ height=”336″ class=”wp-image-11154″ style=”max-width:100%;height:auto;border-radius:8px;” /><figcaption>ภาพจากหน้าจอ Excel จริง (ตัวอย่างข้อมูลในวิดีโอ)</figcaption></figure>
<p>ในตัวอย่างนี้ <code>(A2:A5="กรุงเทพ")+(A2:A5="เชียงใหม่")</code> คือเงื่อนไขแบบ OR ระหว่างสองสาขา เมื่อบวกกันจะได้ 1 ถ้าเข้าเงื่อนไขใดเงื่อนไขหนึ่ง จากนั้นคูณต่อด้วยเงื่อนไข <code>B2:B5>1000</code> แบบ AND แล้วคูณด้วยคอลัมน์ค่าคอมมิชชั่นจริง ผลลัพธ์ที่ผ่านเงื่อนไขครบทั้งสองแถว (กรุงเทพแถว 2 กับเชียงใหม่แถว 5) จะถูกรวมเป็น 300 + 400 = 700 ส่วนแถวที่ไม่เข้าเงื่อนไขจะถูกคูณด้วย 0 ไปโดยอัตโนมัติ ถ้าอยากทบทวนพื้นฐานเงื่อนไขแบบ AND ก่อน สามารถอ่านเพิ่มได้ที่ <a href=COUNTIFS และ SUMIFS นับ/รวมข้อมูลหลายเงื่อนไข

นับจำนวนรายการด้วยเงื่อนไขซับซ้อนที่ COUNTIFS ทำไม่ได้ตรงๆ

SUMPRODUCT ไม่ได้ใช้แค่รวมยอด แต่ใช้นับจำนวนรายการที่เข้าเงื่อนไขซับซ้อนได้ด้วย โดยตัดคอลัมน์ตัวคูณสุดท้ายออก ให้เหลือแค่ผลของเงื่อนไขที่เป็น TRUE/FALSE คูณกันเอง

ตัวอย่างสูตร =SUMPRODUCT((B2:B5="พนักงาน")*(C2:C5>=25)) ใน Excel (ภาพจากหน้าจอจริง)” width=”1882″ height=”366″ class=”wp-image-11153″ style=”max-width:100%;height:auto;border-radius:8px;” /><figcaption>ภาพจากหน้าจอ Excel จริง (ตัวอย่างข้อมูลในวิดีโอ)</figcaption></figure>
<p>สูตรนี้เช็คสองเงื่อนไขพร้อมกันคือสถานะต้องเป็น “พนักงาน” และอายุต้อง 25 ปีขึ้นไป ได้ผลลัพธ์ 2 คน (ใบเตยกับวีระ) ถ้าเงื่อนไขมีแค่แบบ AND ตรงไปตรงมาอย่างนี้ ปกติจะใช้ COUNTIFS ก็ได้เหมือนกัน แต่พอเมื่อไหร่ต้องผสม OR เข้าไปด้วย หรือมีการคำนวณระหว่างคอลัมน์ (เช่น เทียบสองคอลัมน์กันเอง) SUMPRODUCT จะยืดหยุ่นกว่ามาก ลองเทียบวิธีคิดเงื่อนไขเดี่ยวๆ ได้จาก <a href=สูตร 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

ดูเพิ่มเติมที่ Array Formula แบบเดิม (Legacy)

สรุป

SUMPRODUCT คือสูตรที่คูณค่าคู่กันในแต่ละแถวของ array แล้วบวกรวมให้เสร็จในขั้นตอนเดียว เหมาะมากเวลาต้องคำนวณผลรวมแบบถ่วงน้ำหนัก (จำนวน x ราคา) หรือเวลาต้องเช็คเงื่อนไขซับซ้อนแบบ OR ปนกับ AND ที่ SUMIFS/COUNTIFS ทำตรงๆ ไม่ได้ แค่จำไว้ว่าทุก array ต้องมีขนาดเท่ากันเสมอ ก็ใช้งานได้อย่างมั่นใจ ใครอยากทบทวนสูตรรวม/นับข้อมูลตามเงื่อนไขพื้นฐานก่อน แนะนำให้อ่านเพิ่มที่ 10 สูตร Excel พื้นฐานที่ต้องรู้ไว้ใช้ทำงานประจำวัน

หมายเหตุ: บทความนี้เขียนและเรียบเรียงโดย AI

← กลับหน้าบทความ
Line