รวมค่าตามหลายเงื่อนไขด้วย SUMIFS

คุณทราบอยู่แล้วว่า SUMIF ใช้หาผลรวมของค่าที่ตรงกับกฎเดียว เช่น ยอดขายทั้งหมดจากภูมิภาคตะวันออก แต่คำถามในสถานการณ์จริงมักมีหลายเงื่อนไข: ยอดขายของภูมิภาคตะวันออกในเดือนมกราคมเป็นเท่าไร นั่นคือสองเงื่อนไขในคราวเดียว

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

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

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

• sum_range — ค่าที่ต้องการหาผลรวม
• criteria_range1, criteria1 — การตรวจสอบข้อแรก
• criteria_range2, criteria2 — การตรวจสอบข้อที่สอง

คุณสามารถเพิ่มคู่ช่วงและเงื่อนไขได้เรื่อย ๆ จนถึง 127 เงื่อนไข ลองอ่านรูปแบบด้านล่างออกเสียงว่า: หาผลรวมนี้ โดยที่ค่านี้เท่ากับค่านั้น และค่านี้เท่ากับค่านั้น

ลองนึกถึงตารางที่คอลัมน์ A เก็บข้อมูลภูมิภาค คอลัมน์ B เก็บข้อมูลเดือน และคอลัมน์ C เก็บข้อมูลจำนวนเงิน คุณต้องการหายอดขายรวมของภูมิภาคตะวันออกในเดือนมกราคม

จำนวนเงินที่จะนำมาบวกอยู่ใน C:C เงื่อนไขแรกตรวจสอบ A:A ว่าเป็น "ตะวันออก" และเงื่อนไขที่สองตรวจสอบ B:B ว่าเป็น "มกราคม" แถวนั้นจะถูกนับก็ต่อเมื่อเงื่อนไขทั้งสองเป็นจริง

นี่คือข้อผิดพลาดของ SUMIFS ที่พบบ่อยที่สุด sum_range และ criteria_range ทุกช่วงต้องมีมิติเท่ากัน กล่าวคือต้องมีจำนวนแถวและคอลัมน์เท่ากัน

หาก sum_range ของคุณคือ C2:C100 แต่ช่วงเงื่อนไขเป็น A2:A99 สูตรจะส่งคืนข้อผิดพลาด #VALUE! เพราะจำนวนแถวไม่ตรงกัน

แนวทางที่ปลอดภัยที่สุดคือใช้แถวเริ่มต้นและแถวสิ้นสุดเดียวกันสำหรับทุกช่วง หรือใช้คอลัมน์ทั้งหมด เช่น A:A อย่างสม่ำเสมอ

การเขียน "ตะวันออก" ไว้โดยตรงในเครื่องหมายอัญประกาศใช้ได้ แต่รายงานที่ยืดหยุ่นจะเปิดให้ผู้ใช้เลือกได้ ให้ใส่ภูมิภาคในเซลล์ F1 และเดือนใน F2 แล้วอ้างอิงเซลล์เหล่านั้นเป็นเงื่อนไข

เมื่อเปลี่ยน F1 หรือ F2 ผลรวมจะคำนวณใหม่ทันที โปรดสังเกตว่าการอ้างอิงเซลล์โดยตรงไม่ต้องใช้เครื่องหมายอัญประกาศ — เครื่องหมายอัญประกาศใช้เฉพาะกับข้อความตามตัวอักษรที่พิมพ์ไว้ภายในสูตรเท่านั้น

เงื่อนไขไม่ได้จำกัดอยู่แค่ข้อความที่ตรงกันทุกประการ คุณสามารถใช้ตัวดำเนินการเปรียบเทียบกับตัวเลขได้โดยครอบตัวดำเนินการเหล่านั้นด้วยเครื่องหมายอัญประกาศ

• \">100\" — มากกว่า 100
• \"<=50\" — น้อยกว่าหรือเท่ากับ 50
• \"<>0\" — ไม่เท่ากับศูนย์

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

หากค่าขีดจำกัดอยู่ในเซลล์แทนที่จะพิมพ์ไว้โดยตรงจะทำอย่างไร คุณไม่สามารถเขียน \">F1\" ได้ เพราะนั่นจะค้นหาข้อความตามตัวอักษรว่า F1 แต่คุณต้องเชื่อมตัวดำเนินการกับเซลล์ด้วยสัญลักษณ์ &

ดังนั้น \">\"&F1 จะสร้างเงื่อนไข มากกว่าค่าที่อยู่ใน F1 เทคนิคการต่อข้อความนี้จำเป็นอย่างยิ่งสำหรับรายงานแบบไดนามิกที่ผู้ใช้เป็นผู้กำหนด

SUMIFS รองรับการขยายเงื่อนไขได้ง่าย ให้เพิ่มคู่ช่วงและเงื่อนไขอีกหนึ่งคู่สำหรับกฎใหม่แต่ละข้อ สมมติว่าคอลัมน์ D เก็บข้อมูลพนักงานขาย คุณสามารถหาผลรวมยอดขายของภูมิภาคตะวันออกในเดือนมกราคมที่ทำโดย "มาเรีย" ได้

เงื่อนไขแต่ละข้อจะจำกัดผลลัพธ์ให้แคบลงเรื่อย ๆ เนื่องจาก SUMIFS ใช้ตรรกะ AND แถวหนึ่งจึงต้องผ่านการตรวจสอบทั้งสามข้อจึงจะถูกนำมารวมในผลลัพธ์

เงื่อนไขข้อความรองรับอักขระแทน เครื่องหมายดอกจัน * จับคู่อักขระจำนวนเท่าใดก็ได้ และเครื่องหมายคำถาม ? จับคู่อักขระได้พอดีหนึ่งตัว

• \"North*\" จับคู่กับ North, Northeast, Northwest
• \"*east*\" จับคู่กับข้อความใดก็ตามที่มี east อยู่ภายใน

สูตรนี้หาผลรวมของจำนวนเงินสำหรับภูมิภาคใดก็ตามที่ขึ้นต้นด้วย "North" ซึ่งมีประโยชน์เมื่อชื่อภูมิภาคใช้คำนำหน้าร่วมกัน

ควรจำความแตกต่างนี้ให้ขึ้นใจ เพราะลำดับอาร์กิวเมนต์กลับกัน:

• SUMIF: range, criteria, [sum_range] — ช่วงที่จะตรวจสอบอยู่ก่อน ส่วน sum_range เป็นอาร์กิวเมนต์เสริมและอยู่ท้ายสุด
• SUMIFS: sum_range, criteria_range1, criteria1, ... — sum_range อยู่ก่อนเสมอ

เคล็ดลับ: หากมีมากกว่าหนึ่งเงื่อนไข ให้เลือกใช้ SUMIFS โดยตรง หลายคนใช้ SUMIFS แม้มีเพียงเงื่อนไขเดียว เพื่อให้ใช้รูปแบบเดียวกันอย่างสม่ำเสมอ

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

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

ทดสอบความเข้าใจของคุณเกี่ยวกับลำดับอาร์กิวเมนต์ของ SUMIFS

ตอนนี้คุณสามารถหาผลรวมตัวเลขที่ตรงกับหลายเงื่อนไขได้ในคราวเดียว:

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — sum_range อยู่ก่อน
• ช่วงทั้งหมดต้องมีขนาดเท่ากัน มิฉะนั้นจะเกิด #VALUE!
• ใช้ \">\"&F1 เพื่อเปรียบเทียบกับเซลล์ และใช้ * / ? สำหรับอักขระแทน
• เงื่อนไขใช้ตรรกะ AND — แถวหนึ่งต้องผ่านทุกเงื่อนไข

ถัดไป: การนับแถวที่ตรงกับหลายเงื่อนไขด้วย COUNTIFS