นับตามหลายเงื่อนไขด้วย COUNTIFS

COUNTIFS ใช้นับจำนวนแถวที่ตรงกับเงื่อนไขหลายข้อในเวลาเดียวกัน ในขณะที่ COUNTIF ตอบคำถามว่า "มียอดขายในภูมิภาคตะวันออกกี่รายการ" COUNTIFS จะตอบว่า "มียอดขายของภูมิภาคตะวันออกในเดือนมกราคมกี่รายการ"

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

มาเรียนรู้โครงสร้างและนำไปใช้งานกัน

COUNTIFS ไม่มีช่วงผลรวม เพราะไม่ได้บวกค่าใด ๆ แต่ต้องการเพียงช่วงที่จะตรวจสอบและเงื่อนไขที่จะใช้ตรวจสอบช่วงเหล่านั้น

• criteria_range1, criteria1 — การตรวจสอบข้อแรก
• criteria_range2, criteria2 — การตรวจสอบข้อที่สอง
• ...และต่อไปเรื่อย ๆ ได้สูงสุด 127 คู่

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

ใช้ตารางยอดขายเดิม โดยคอลัมน์ A คือภูมิภาค คอลัมน์ B คือเดือน และคอลัมน์ C คือจำนวนเงิน มานับกันว่ามียอดขายในภูมิภาคตะวันออกและในเดือนมกราคมกี่รายการ

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

เช่นเดียวกับ SUMIFS COUNTIFS รองรับตัวดำเนินการเปรียบเทียบภายในเครื่องหมายอัญประกาศสำหรับการตรวจสอบตัวเลข

• \">100\" — ค่าที่มากกว่า 100
• \">=18\" — 18 ปีขึ้นไป
• \"<>\" — เซลล์ที่ไม่ว่างใด ๆ

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

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

สูตรนี้นับจำนวนเงินที่อย่างน้อย 50 และไม่เกิน 200 เงื่อนไขทั้งสองชี้ไปยังคอลัมน์ C และเนื่องจาก COUNTIFS ใช้ตรรกะ AND จึงนับเฉพาะแถวที่อยู่ภายในช่วงดังกล่าว

หากต้องการให้ขอบเขตเปลี่ยนแปลงได้ ให้เก็บค่าเหล่านั้นไว้ในเซลล์และเชื่อมตัวดำเนินการด้วย & ใส่ค่าต่ำสุดใน F1 และค่าสูงสุดใน F2

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

เงื่อนไขพิเศษสองข้อมีประโยชน์สำหรับการตรวจสอบคุณภาพข้อมูล:

• \"<>\" ใช้นับเซลล์ที่ไม่ว่าง
• \"=\" หรือ \"\" ใช้นับเซลล์ที่ว่าง

สูตรนี้นับจำนวนแถวของภูมิภาคตะวันออกที่มีการกรอกข้อมูลพนักงานขายไว้แล้ว (คอลัมน์ D ไม่ว่าง) ซึ่งเป็นวิธีตรวจหาข้อมูลที่ขาดหายได้อย่างรวดเร็ว

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

สูตรนี้นับทุกแถวที่ชื่อพนักงานขายขึ้นต้นด้วยตัวอักษร M — ชื่อ Maria, Mike และ Mohammed ล้วนตรงกัน อักขระแทนมีผลเฉพาะกับข้อความเท่านั้น และจะถูกละเว้นเมื่อใช้กับตัวเลข

กฎเรื่องมิติเดียวกันจาก SUMIFS ใช้กับกรณีนี้ด้วย: criteria_range ทุกช่วงต้องมีรูปแบบเดียวกัน หากช่วงหนึ่งคือ A2:A100 และอีกช่วงคือ B2:B50 COUNTIFS จะส่งคืนข้อผิดพลาด #VALUE!

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

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

การตรึงคอลัมน์ด้วย $A:$A และตรึงเซลล์สำหรับค้นหาด้วย $F2 / G$1 ช่วยให้สูตรเดียวเติมข้อมูลลงในตารางไขว้ของจำนวนทั้งหมดได้เมื่อคัดลอกสูตร

โปรดแยกแยะฟังก์ชันในกลุ่มนี้ให้ถูกต้อง COUNTIF รับคู่ range, criteria เพียงหนึ่งคู่ ส่วน COUNTIFS รับตั้งแต่หนึ่งคู่ขึ้นไป ต่างจาก SUMIFS ทั้งสองฟังก์ชันไม่มีช่วงผลลัพธ์แยกต่างหาก เพราะทำหน้าที่นับอย่างเดียว

หากต้องการมากกว่าหนึ่งเงื่อนไข ให้เลือกใช้ COUNTIFS หลายคนใช้ COUNTIFS แม้มีการตรวจสอบเพียงข้อเดียว เพื่อจะได้จำรูปแบบเดียวกันอย่างสม่ำเสมอสำหรับ SUM, COUNT และ AVERAGE

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

• COUNTIFS(range1, crit1, range2, crit2, ...) — ไม่มี sum_range มีเพียงคู่สำหรับตรวจสอบ
• นับช่วงแบบระหว่างค่าโดยระบุคอลัมน์เดียวกันซ้ำพร้อมขอบเขตสองค่า
• ใช้ \"<>\" สำหรับเซลล์ที่ไม่ว่าง และเชื่อมด้วย & สำหรับขีดจำกัดที่อ้างอิงจากเซลล์
• ช่วงทั้งหมดต้องมีขนาดเท่ากัน

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