Thep Excel
COUNTIFS – ฟังก์ชันนับเซลล์ตามหลายเงื่อนไขพร้อมกัน
COUNTIFS นับจำนวนเซลล์ที่ตรงกับหลายเงื่อนไขพร้อมกัน โดยใช้ AND logic หมายความว่าเงื่อนไขทุกข้อต้องเป็นจริงถึงจะนับ ซึ่งต่างจาก COUNTIF ที่มีได้แค่เงื่อนไขเดียว
.
ข้อดีคือรองรับได้ถึง 127 คู่ criteria_range/criteria ทำให้วิเคราะห์ข้อมูลซับซ้อนได้อย่างมีประสิทธิภาพ รองรับ wildcard characters (* แทนตัวอักษรกี่ตัวก็ได้, ? แทนตัวอักษรหนึ่งตัว) และ operators (>, =, <=, ) สำหรับเปรียบเทียบตัวเลขและวันที่
.
ที่ต้องระวังคือ criteria_range ทุกตัวต้องมีขนาดเท่ากันทุกประการ (rows × columns) มิฉะนั้นจะเกิด #VALUE! error ทันที COUNTIFS เป็นส่วนหนึ่งของ IFS family (SUMIFS, AVERAGEIFS, MAXIFS, MINIFS) ที่มี syntax คล้ายกัน เหมาะมากสำหรับสร้าง dashboard แบบ real-time และวิเคราะห์ KPI หลายมิติ
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Function Metrics
Popularity
9/10
Difficulty
5/10
Usefulness
9/10
Syntax & Arguments
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| criteria_range1 | Range | Yes | ช่วงข้อมูลแรกที่จะตรวจสอบตามเงื่อนไข ต้องเป็น cell range (ไม่ใช่ array) และต้องมีขนาด (จำนวน rows × columns) เท่ากับทุก criteria_range อื่นๆ ในฟังก์ชันเดียวกัน รองรับการอ้างอิง table column (Table[Column]) หรือ named range | |
| criteria1 | Number/Text/Expression/Cell Reference | Yes | เงื่อนไขสำหรับ criteria_range1 รองรับหลายรูปแบบ: ตัวเลข (100), ข้อความ (“Apple”), นิพจน์เปรียบเทียบ (“>100”, “<=50", "0”), cell reference (A1), wildcard pattern (“*ไทย*”, “????”), หรือเซลล์ว่าง (“”) Operators ต้องอยู่ภายใน quotes เสมอ เช่น “>100” ไม่ใช่ >100 สำหรับ dynamic criteria ให้ใช้ & concatenate เช่น “>”&B2 | |
| criteria_range2 | Range | Optional | ช่วงข้อมูลเพิ่มเติมสำหรับเงื่อนไขที่ 2 ต้องมีขนาดเท่ากับ criteria_range1 ทุกประการ (จำนวน rows และ columns เท่ากัน) criteria_range ทั้งหมดไม่จำเป็นต้องติดกัน แต่ต้องมีขนาดตรงกัน รองรับได้สูงสุด 127 คู่ criteria_range/criteria | |
| criteria2 | Number/Text/Expression/Cell Reference | Optional | เงื่อนไขสำหรับ criteria_range2 รูปแบบเดียวกับ criteria1 สามารถใช้ criteria_range เดียวกันหลายครั้งกับ criteria ต่างกัน เช่น COUNTIFS(A:A, “>60”, A:A, “<=80") เพื่อนับค่าในช่วง 60-80 |
How it works
สร้างรายงานยอดขายตามเงื่อนไข
นับจำนวนรายการขายที่อยู่ในช่วงวันที่ ภูมิภาค และสถานะที่กำหนด เหมาะสำหรับทำ dashboard แบบ real-time
ตรวจสอบคุณภาพข้อมูล
นับจำนวนแถวที่กรอกข้อมูลครบถ้วน หรือตรวจสอบข้อมูลที่ผิดปกติตามหลายเงื่อนไข
วิเคราะห์ KPI และ metrics
นับจำนวนงานที่อยู่ใน SLA, นับจำนวน tasks ตาม priority และ status, หรือนับ tickets ที่ยังไม่จบภายในกำหนด
จัดทำสถิติข้อมูล
นับจำนวนนักเรียนที่ได้คะแนนในช่วงที่กำหนด (เกรด A, B, C) หรือนับจำนวนผลิตภัณฑ์ตามหมวดหมู่และสต็อก
วิเคราะห์พฤติกรรมลูกค้า
นับจำนวนลูกค้าที่ซื้อสินค้าในช่วงเวลา ช่วงราคา และหมวดหมู่ที่กำหนด เพื่อวิเคราะห์แนวโน้ม
Examples
ตัวอย่างที่ 1: พื้นฐาน – เงื่อนไขเดียว (เทียบเท่า COUNTIF)
COUNTIFS(Sales[Status], "Completed")
.
แม้ว่า COUNTIFS จะออกแบบมาสำหรับหลายเงื่อนไข แต่ก็ใช้กับเงื่อนไขเดียวได้เหมือน COUNTIF เปี๊ยบ ส่วนตัวผมแนะนำให้ใช้ COUNTIFS เสมอครับ เพราะถ้าวันหนึ่งต้องเพิ่มเงื่อนไขอื่น ก็แค่เติมต่อท้ายได้เลย ไม่ต้องมาเปลี่ยนฟอร์มูลาใหม่ 😎
=COUNTIFS(Sales[Status], "Completed")
47
ตัวอย่างที่ 2: สองเงื่อนไข – AND logic พื้นฐาน
COUNTIFS(Sales[Region], "North", Sales[Amount], ">1000")
.
สังเกตว่า operator > ต้องอยู่ใน quotes เป็น ">1000" เสมอนะครับ ถ้าเขียนแค่ >1000 (ไม่มี quotes) Excel จะ error ทันที นี่คือรูปแบบพื้นฐานของ COUNTIFS ที่ใช้บ่อยที่สุด
=COUNTIFS(Sales[Region], "North", Sales[Amount], ">1000")
23
ตัวอย่างที่ 3: สามเงื่อนไข – ตรวจสอบหลายมิติ
COUNTIFS(Sales[Region], "North", Sales[Amount], ">1000", Sales[Status], "Completed")
=COUNTIFS(Sales[Region], "North", Sales[Amount], ">1000", Sales[Status], "Completed")
18
ตัวอย่างที่ 4: ช่วงตัวเลข – Between (ใช้ range เดียวกันสองครั้ง)
COUNTIFS(Scores[Score], ">60", Scores[Score], "
=COUNTIFS(Scores[Score], ">60", Scores[Score], "<=80")
35
ตัวอย่างที่ 5: ช่วงวันที่ – ใช้ DATE function
COUNTIFS(Orders[Date], ">="&DATE(2024,1,1), Orders[Date], "
=COUNTIFS(Orders[Date], ">="&DATE(2024,1,1), Orders[Date], "<"&DATE(2024,2,1))
142
ตัวอย่างที่ 6: Wildcard – ค้นหา pattern ข้อความ
COUNTIFS(Products[Name], "iPhone*", Products[Stock], ">0")
=COUNTIFS(Products[Name], "iPhone*", Products[Stock], ">0")
12
ตัวอย่างที่ 7: Wildcard – ค้นหาข้อความที่มีคำใดๆ ในนั้น
COUNTIFS(Customers[Company], "*กรุงเทพ*", Customers[Status], "Active")
=COUNTIFS(Customers[Company], "*กรุงเทพ*", Customers[Status], "Active")
28
ตัวอย่างที่ 8: ตรวจสอบเซลล์ว่างและไม่ว่าง
COUNTIFS(Forms[Name], "", Forms[Email], "", Forms[Phone], "")
=COUNTIFS(Forms[Name], "<>", Forms[Email], "<>", Forms[Phone], "<>")
156
ตัวอย่างที่ 9: Dynamic criteria – ใช้ cell reference
COUNTIFS(Sales[Region], B2, Sales[Amount], ">"&C2, Sales[Date], ">="…
=COUNTIFS(Sales[Region], B2, Sales[Amount], ">"&C2, Sales[Date], ">="&D2)
15
ตัวอย่างที่ 10: OR logic – ใช้ SUM กับ array constant
SUM(COUNTIFS(Tickets[Status], {"Open","In Progress","Pending"}))
=SUM(COUNTIFS(Tickets[Status], {"Open","In Progress","Pending"}))
89
ตัวอย่างที่ 11: Advanced – Multiple wildcards และ range
COUNTIFS(Products[SKU], "PRD-???-*", Products[Category], "Electronics", Products[Price], ">=10000", Products[Price], "0")
=COUNTIFS(Products[SKU], "PRD-???-*", Products[Category], "Electronics", Products[Price], ">=10000", Products[Price], "<=50000", Products[Stock], ">0")
42
ตัวอย่างที่ 12: Real-world – Dashboard summary
COUNTIFS(Projects[StartDate], ">="&DATE(YEAR(TODAY()),1,1), Projects[StartDate], "1000000", Projects[Department], B5)
=COUNTIFS(Projects[StartDate], ">="&DATE(YEAR(TODAY()),1,1), Projects[StartDate], "<="&TODAY(), Projects[Status], "Completed", Projects[Budget], ">1000000", Projects[Department], B5)
7
FAQs
COUNTIFS ต่างจาก COUNTIF อย่างไร และควรใช้อันไหน?
COUNTIF รองรับได้แค่หนึ่งเงื่อนไข (criteria_range, criteria) เหมาะสำหรับงานง่ายๆ เช่น =COUNTIF(A:A, “Red”)
.
ส่วน COUNTIFS รองรับได้สูงสุด 127 คู่เงื่อนไข เหมาะสำหรับการวิเคราะห์ที่ซับซ้อน เช่น =COUNTIFS(A:A, “Red”, B:B, “>100”, C:C, “Active”)
.
ถ้ามีเงื่อนไขเดียว ทั้งสองฟังก์ชันทำงานเหมือนกันเลย แต่ส่วนตัวผมแนะนำให้ใช้ COUNTIFS เสมอครับ เพราะ (1) รองรับการเพิ่มเงื่อนไขในอนาคต (2) syntax สอดคล้องกับ SUMIFS, AVERAGEIFS ที่ใช้บ่อย (3) เป็นมาตรฐานใหม่ตั้งแต่ Excel 2007 อยู่แล้ว 😎
ทำไมได้ #VALUE! error และแก้ไขอย่างไร?
ปัญหานี้เจอบ่อยมากครับ 😅 #VALUE! error ใน COUNTIFS มักเกิดจาก 3 สาเหตุหลักๆ:
.
(1) ขนาด criteria_range ไม่เท่ากัน – นี่คือสาเหตุอันดับ 1 เลย ทุก criteria_range ต้องมีจำนวน rows และ columns เท่ากันทุกประการ เช่น ถ้า criteria_range1 = A1:A100 (100 rows) ทุก criteria_range อื่นต้องเป็น 100 rows เช่นกัน วิธีแก้: ตรวจสอบ range ทุกตัวด้วย ROWS() และ COLUMNS()
.
(2) อ้างอิง workbook ภายนอกที่ปิดอยู่ – COUNTIFS ไม่รองรับ closed workbook วิธีแก้: เปิดไฟล์ที่อ้างอิงหรือ copy ข้อมูลมาไว้ workbook เดียวกัน
.
(3) criteria ยาวเกิน 255 ตัวอักษร – Excel จำกัด string length วิธีแก้: ใช้ helper column หรือแยกเงื่อนไขออก
COUNTIFS แยกตัวพิมพ์ใหญ่-เล็ก (case-sensitive) หรือไม่?
ไม่แยกครับ COUNTIFS เป็น case-insensitive เช่น “RED”, “Red”, “red”, “rEd” มันถือว่าเป็นค่าเดียวกันหมด
.
ดังนั้น =COUNTIFS(A:A, “Apple”) จะนับทั้ง “Apple”, “apple”, “APPLE” รวมกันไปหมด 😅
.
ถ้าต้องการนับแบบ case-sensitive (แยกตัวพิมพ์ใหญ่-เล็ก) ให้ใช้ SUMPRODUCT ร่วมกับ EXACT แทน เช่น =SUMPRODUCT((EXACT(A2:A100,”Apple”))*(B2:B100>100)) จะนับเฉพาะ “Apple” (ตัวพิมพ์ตรงทุกตัว) ที่คอลัมน์ B >100
.
EXACT เป็นฟังก์ชันเดียวใน Excel ที่แยกตัวพิมพ์ใหญ่-เล็กได้ครับ
จะใช้ wildcard ได้อย่างไร และมีอะไรบ้าง?
COUNTIFS รองรับ wildcard 3 ตัวครับ:
.
(1) * (asterisk) = แทนตัวอักษรกี่ตัวก็ได้ เช่น “*ไทย” = ลงท้ายด้วยไทย, “ไทย*” = ขึ้นต้นด้วยไทย, “*ไทย*” = มีคำว่าไทยอยู่ที่ไหนก็ได้
.
(2) ? (question mark) = แทนตัวอักษร 1 ตัวเท่านั้น เช่น “??-???” = 2 ตัวอักษร ตามด้วย – แล้วตามด้วย 3 ตัวอักษร (เช่น AB-CDE)
.
(3) ~ (tilde) = escape character สำหรับค้นหา * หรือ ? จริงๆ เช่น “~*” = ค้นหาเครื่องหมาย * (ไม่ใช่ wildcard)
.
ตัวอย่าง: =COUNTIFS(A:A, “Prod-???-*”, B:B, “>100”) นับรายการที่ A มี pattern Prod-xxx-อะไรก็ได้ และ B >100
จะนับแบบ OR logic (เงื่อนไขใดเงื่อนไขหนึ่ง) ได้อย่างไร?
COUNTIFS รองรับแค่ AND logic (ทุกเงื่อนไขต้องเป็นจริงพร้อมกัน) ถ้าอยากใช้ OR logic (เงื่อนไขใดเงื่อนไขหนึ่ง) มี 3 วิธีครับ:
.
(1) ใช้ SUM + array constant (เหมาะกับ OR เงื่อนไขเดียว) เช่น =SUM(COUNTIFS(A:A, {“Red”,”Blue”,”Green”})) นับ Red หรือ Blue หรือ Green
.
(2) ใช้ COUNTIF หลายตัวบวกกัน เช่น =COUNTIF(A:A,”Red”)+COUNTIF(A:A,”Blue”)
.
(3) ใช้ SUMPRODUCT สำหรับ OR หลายเงื่อนไข เช่น =SUMPRODUCT(((A2:A100=”Red”)+(A2:A100=”Blue”))*(B2:B100>100)) นับ (Red หรือ Blue) และ B>100
.
ส่วนตัวผมแนะนำให้ใช้ SUMPRODUCT ถ้า logic ซับซ้อน เพราะยืดหยุ่นที่สุดครับ 😎
ทำไมนับตัวเลขยาวๆ (เช่น เลขบัตรประชาชน) ไม่ถูกต้อง?
นี่เป็นปัญหาที่เจอบ่อยมากครับ 😭 Excel จัดเก็บตัวเลขได้แค่ 15 หลัก ถ้ายาวกว่านี้ (เช่น เลขบัตรประชาชน 13 หลัก, เลขบัญชี 16 หลัก) Excel จะปัดเศษหลักที่ 16+ เป็น 0 ทันที ทำให้ 1234567890123456 กลายเป็น 1234567890123450
.
วิธีแก้มี 3 วิธี:
.
(1) จัดเก็บเป็น Text แทน โดยใส่เครื่องหมาย ‘ หน้าตัวเลข (เช่น ‘1234567890123) หรือ format cell เป็น Text ก่อนกรอกข้อมูล
.
(2) ใช้ criteria เป็น text เช่น =COUNTIFS(A:A, “1234567890123”) ไม่ใช่ =COUNTIFS(A:A, 1234567890123)
.
(3) ถ้า import จาก CSV ต้อง import เป็น Text column ตั้งแต่แรก มิฉะนั้นข้อมูลเสียหายแล้วครับ
จะนับเซลล์ว่าง (blank) และไม่ว่าง (non-blank) อย่างไร?
สำหรับเซลล์ว่าง ใช้ criteria เป็น “” (empty string) เช่น =COUNTIFS(A:A, “”) นับเซลล์ว่างในคอลัมน์ A หรือ =COUNTIFS(A:A, “”, B:B, “”) นับแถวที่ A และ B ว่างพร้อมกัน สำหรับเซลล์ไม่ว่าง ใช้ criteria เป็น “” เช่น =COUNTIFS(A:A, “”) นับเซลล์ที่มีค่าใดๆ (ไม่ว่าง) หรือ =COUNTIFS(A:A, “”, B:B, “”, C:C, “”) นับแถวที่ A, B, C ไม่ว่างทั้งหมด (ใช้ตรวจสอบความสมบูรณ์ของข้อมูล) หมายเหตุ: COUNTBLANK ใช้สำหรับนับเซลล์ว่างเท่านั้น ไม่รองรับเงื่อนไขเพิ่มเติม ส่วน COUNTA นับเซลล์ไม่ว่างทั้งหมดโดยไม่มีเงื่อนไข
จะนับวันที่ในช่วงที่กำหนด (date range) อย่างไร?
นับวันที่ในช่วงให้ใช้ criteria_range เดียวกันสองครั้งกับ operators >= และ =”&DATE(2024,1,1), A:A, “=”&TODAY()-30, Status, “Active”) นับรายการ Active ใน 30 วันล่าสุด
COUNTIFS รองรับ array formulas หรือ spill ranges หรือไม่?
ไม่ COUNTIFS ต้องการ static ranges เท่านั้น (cell ranges ที่อ้างอิงตรงๆ) ไม่รองรับ array formulas หรือ dynamic spill ranges ที่ได้จากฟังก์ชัน เช่น =COUNTIFS(FILTER(…), criteria) จะ error เพราะ FILTER คืน array ไม่ใช่ range ถ้าต้องการนับจาก dynamic array ให้ใช้ SUMPRODUCT แทน เช่น =SUMPRODUCT((FILTER(A:B,C:C=”X”)=criteria)*1) หรือใช้ helper column เก็บผลลัพธ์จาก FILTER แล้ว COUNTIFS จาก helper column ตัวอย่าง: แทน =COUNTIFS(UNIQUE(A:A), “>100”) (error) ใช้ =SUMPRODUCT((UNIQUE(A:A)>100)*1) หรือใส่ =UNIQUE(A:A) ในเซลล์ D1 แล้วใช้ =COUNTIFS(D:D, “>100”) แทน ข้อจำกัดนี้เป็น Excel limitation ไม่ใช่ bug
จะใช้ COUNTIFS กับ Table (structured references) อย่างไร?
COUNTIFS ทำงานได้ดีกับ Excel Tables และแนะนำให้ใช้ structured references เสมอเพราะ (1) ชัดเจน อ่านง่าย (2) auto-expand เมื่อเพิ่มแถว (3) ไม่ต้องล็อค range ด้วย $ syntax: =COUNTIFS(TableName[ColumnName], criteria) เช่น =COUNTIFS(Sales[Region], “North”, Sales[Amount], “>1000″) หมายถึงนับในตาราง Sales คอลัมน์ Region=”North” และคอลัมน์ Amount>1000 สามารถใช้ [@ColumnName] สำหรับแถวเดียวกัน เช่น =COUNTIFS(Sales[Region], [@Region]) นับจำนวนรายการที่มี Region เดียวกับแถวปัจจุบัน หรือใช้ Table[#All] สำหรับทั้งตารางรวม header, Table[#Data] สำหรับข้อมูลอย่างเดียว, Table[#Headers] สำหรับ header row ตัวอย่างการผสม: =COUNTIFS(Sales[Region], B2, Sales[Date], “>=”&Sales[[#Headers],[Date]]) นับ region จาก B2 ที่วันที่ >= header column Date
Resources & Related
Resources
Related posts
Related functions
Additional Notes
COUNTIFS เป็นฟังก์ชันที่นับจำนวนเซลล์ที่ตรงตามหลายเงื่อนไขพร้อมกัน โดยใช้ AND logic หมายความว่าเซลล์จะถูกนับก็ต่อเมื่อตรงกับทุกเงื่อนไขที่กำหนด
.
ที่เจ๋งคือ COUNTIFS รองรับได้สูงสุด 127 คู่ของ criteria_range และ criteria ทำให้สามารถวิเคราะห์ข้อมูลที่ซับซ้อนได้อย่างมีประสิทธิภาพ 💡 รองรับการใช้ wildcard characters (* และ ?) สำหรับการค้นหาข้อความแบบ pattern matching และสามารถทำงานกับข้อมูลทุกประเภทไม่ว่าจะเป็นตัวเลข ข้อความ หรือวันที่
.
COUNTIFS แตกต่างจาก COUNTIF ตรงที่รองรับหลายเงื่อนไข ในขณะที่ COUNTIF มีได้เพียงเงื่อนไขเดียว ฟังก์ชันนี้เป็นส่วนหนึ่งของตระกูล IFS functions ที่ประกอบด้วย SUMIFS, AVERAGEIFS, MAXIFS และ MINIFS ซึ่งมีรูปแบบการใช้งานที่คล้ายคลึงกัน
.
สำหรับกรณีที่ต้องการ OR logic (เงื่อนไขใดเงื่อนไขหนึ่ง) สามารถใช้เทคนิค SUM ร่วมกับ array constants หรือใช้ SUMPRODUCT แทนครับ 😎
.
การใช้งาน COUNTIFS มีความยืดหยุ่นสูง สามารถกำหนดช่วงค่า (range) เช่นนับตัวเลขระหว่าง 60-80 นับวันที่ในเดือนใดเดือนหนึ่ง หรือนับข้อความที่มี pattern ที่กำหนด
.
ส่วนตัวผมใช้ COUNTIFS บ่อยมากสำหรับการสร้างรายงาน dashboard ตรวจสอบคุณภาพข้อมูล วิเคราะห์ KPI และจัดทำสถิติข้อมูลแบบ real-time
.
ที่ต้องระวังคือขนาดของ criteria_range ทุกอันต้องมีจำนวน rows และ columns เท่ากันเสมอนะครับ มิฉะนั้นจะได้ #VALUE! error ทันที 😭