รวมค่าตามเกณฑ์ด้วย SUMIF

SUM ปกติจะบวกตัวเลข ทุกตัว ในช่วงข้อมูล แต่บ่อยครั้งคุณต้องการบวกเพียงบางตัวเท่านั้น ลองนึกภาพตารางยอดขายที่คอลัมน์ A มีภูมิภาค และคอลัมน์ B มียอดเงิน คุณไม่ได้ต้องการยอดขายรวมทั้งหมด แต่ต้องการเฉพาะยอดรวมของภูมิภาค ตะวันออก

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

SUMIF รับส่วนประกอบสามรายการตามลำดับ:

• ช่วงข้อมูล เซลล์ที่จะใช้ตรวจสอบกับเงื่อนไข
• เงื่อนไข เงื่อนไขที่ต้องการค้นหา
• ช่วงผลรวม เซลล์ที่จะนำมาบวกจริง

ดังนั้น ตารางจะตรวจดู ช่วงข้อมูล ค้นหาแถวที่ตรงตาม เงื่อนไข แล้วรวมค่าเซลล์ที่ตรงกันจาก ช่วงผลรวม

สมมติว่าชื่อภูมิภาคอยู่ใน A2:A10 และยอดขายอยู่ใน B2:B10 หากต้องการรวมเฉพาะยอดขายของภูมิภาคตะวันออก ให้ตรวจสอบคอลัมน์ A ว่ามีคำว่า East หรือไม่ แล้วบวกยอดเงินที่ตรงกันจากคอลัมน์ B

ตารางจะตรวจสอบเซลล์ภูมิภาคทีละเซลล์ เมื่อพบค่า East ก็จะนำยอดเงินของแถวนั้นมาบวกเข้ากับผลรวมสะสม ภูมิภาคอื่นจะถูกละเว้น

เมื่อเงื่อนไขของคุณเป็นคำ ให้ใส่ไว้ในเครื่องหมายคำพูดคู่: "East" หากไม่ใส่เครื่องหมายคำพูด ตารางจะคิดว่า East เป็นชื่อหรือป้ายกำกับ และอาจคืนค่าเป็นศูนย์

การจับคู่ไม่แยกแยะตัวพิมพ์เล็ก-ใหญ่ ดังนั้น "east" และ "East" จึงตรงกับแถวเดียวกัน เพียงตรวจสอบให้แน่ใจว่าการสะกดตรงกับข้อมูลทุกประการ รวมถึงไม่มีช่องว่างเกินมา

การพิมพ์เงื่อนไขลงไปโดยตรงก็ทำได้ แต่การใส่เงื่อนไขไว้ในเซลล์จะเป็นระเบียบกว่า หาก D1 มีคำว่า East ให้ใช้อ้างอิง D1 แทนการเขียนข้อความลงในสูตร

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

เงื่อนไขไม่จำเป็นต้องเป็นข้อความเสมอไป หากต้องการรวมยอดเงินทุกจำนวนที่เท่ากับ 500 พอดี ให้ใช้ตัวเลขนั้นเป็นเงื่อนไขได้เลย

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

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

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

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

หากค่าขีดจำกัดอยู่ในเซลล์ เช่น D1 มีค่า 100 และคุณต้องการหายอดเงินที่มากกว่าค่านั้น คุณจะเขียน ">D1" ไม่ได้ เพราะระบบจะตีความเป็นข้อความ D1 ตามตัวอักษร แต่ให้เชื่อมตัวดำเนินการกับเซลล์ด้วยเครื่องหมายแอมเพอร์แซนด์แทน

เครื่องหมายแอมเพอร์แซนด์จะนำข้อความ ">" ไปต่อกับค่าภายใน D1 เพื่อสร้างเงื่อนไข ">100" ขึ้นขณะคำนวณ

ช่วงข้อมูล และ ช่วงผลรวม ควรมีความสูงเท่ากันและเริ่มต้นจากแถวเดียวกัน หากชื่อภูมิภาคอยู่ใน A2:A10 ยอดเงินต้องอยู่ใน B2:B10 ไม่ใช่ B3:B11

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

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

ใส่ป้ายกำกับแต่ละหมวดหมู่ในเซลล์ D2, D3, D4 แล้วเขียน SUMIF หนึ่งสูตรที่อ้างอิงเซลล์ป้ายกำกับ จากนั้นเติมสูตรลงด้านล่าง แต่ละแถวก็จะรวมยอดของหมวดหมู่ของตนเอง ใช้รูปแบบสูตรเดียวเพื่อสร้างผลรวมย่อยสามรายการได้ทันที

สังเกตว่าตัวอย่างล่าสุดใช้ A:A และ B:B ซึ่งเป็นคอลัมน์ทั้งหมด วิธีนี้สะดวกเมื่อมีการเพิ่มแถวอยู่เรื่อย ๆ เพราะคุณไม่ต้องขยายช่วงข้อมูลเอง

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

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

ตอนนี้คุณรู้วิธีบวกตัวเลขตามเงื่อนไขด้วย SUMIF แล้ว ประเด็นสำคัญมีดังนี้:

• ลำดับคือ ช่วงข้อมูล, เงื่อนไข, ช่วงผลรวม
• เงื่อนไขข้อความต้องใส่เครื่องหมายคำพูด และการจับคู่จะไม่แยกแยะตัวพิมพ์เล็ก-ใหญ่
• ตัวดำเนินการอย่าง ">100" จะอยู่ภายในเครื่องหมายคำพูด หากต้องการเชื่อมตัวดำเนินการกับเซลล์ ให้ใช้ &
• จัดช่วงข้อมูลและช่วงผลรวมให้ตรงกับแถวเดียวกัน

ถัดไป คุณจะได้นับแถวที่ตรงตามเงื่อนไขด้วย COUNTIF