Thep Excel
SUMIFS – ฟังก์ชันบวกตามหลายเงื่อนไข
SUMIFS บวกค่าจาก sum_range เฉพาะแถวที่ตรงตามเงื่อนไขทุกข้อพร้อมกัน (AND logic) รองรับได้สูงสุด 127 คู่เงื่อนไข สามารถใช้ comparison operators (>, =, <=, ), wildcard characters (*, ?), และ cell references ใน criteria ได้ เหมาะสำหรับการวิเคราะห์ข้อมูลแบบ multi-dimensional filtering เช่น รายงานยอดขายตามภูมิภาค ช่วงเวลา และสถานะพร้อมกัน โดยไม่ต้องใช้ helper columns หรือฟังก์ชันซ้อนซับซ้อน
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Function Metrics
Popularity
9/10
Difficulty
5/10
Usefulness
9/10
Syntax & Arguments
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| sum_range | Range | Yes | ช่วงตัวเลขที่ต้องการบวก (ต้องมีขนาดเท่ากับทุก criteria_range มิฉะนั้นจะได้ #VALUE! error) | |
| criteria_range1 | Range | Yes | ช่วงแรกที่จะตรวจสอบตามเงื่อนไข (ต้องมีขนาดเท่ากับ sum_range) | |
| criteria1 | Text/Number/Date/Cell Reference | Yes | เงื่อนไขที่ 1 สามารถเป็นตัวเลข, ข้อความ (ต้องครอบด้วย double quotes), comparison operators (“>100”, “0”), wildcard patterns (“*Pro*”, “A???”), cell references, หรือ DATE function ร่วมกับ & operator | |
| criteria_range2 | Range | Optional | ช่วงที่ 2 สำหรับเงื่อนไขเพิ่มเติม (optional, ต้องมีขนาดเท่ากับ sum_range) | |
| criteria2 | Text/Number/Date/Cell Reference | Optional | เงื่อนไขที่ 2 (optional, ใช้รูปแบบเดียวกับ criteria1) |
How it works
ยอดขายตามภูมิภาคและช่วงเวลา
วิเคราะห์ยอดขายรายภูมิภาคในช่วงเวลาเฉพาะ เช่น North ในเดือน ม.ค. 2024 โดยไม่ต้องใช้ PivotTable
คัดกรองค่าใช้จ่ายตามหมวดและสถานะ
รวมค่าใช้จ่ายเฉพาะหมวดที่กำหนดและผ่านการอนุมัติแล้ว เหมาะสำหรับรายงานทางการเงิน
สรุปคะแนนสอบตามวิชาและเกณฑ์
รวมคะแนนที่ผ่านเกณฑ์ขั้นต่ำและเป็นวิชาที่ต้องการวิเคราะห์
Inventory ตามสินค้าและระดับ stock
คำนวณมูลค่า inventory เฉพาะสินค้าประเภทหนึ่งที่มี stock ต่ำกว่าเกณฑ์
Dynamic Dashboard กรองหลายมิติ
สร้าง dashboard ที่ผู้ใช้เปลี่ยน criteria ผ่าน cell references (dropdowns, input cells) และสูตรคำนวณแบบ real-time
Examples
ตัวอย่างที่ 1: Foundation – รวมยอดขายตามภูมิภาคเดียว
SUMIFS(Sales[Amount], Sales[Region], "North")
.
นี่คือการใช้งานพื้นฐานที่สุดของ SUMIFS แม้จะมีเงื่อนไขเดียว (ซึ่งทำด้วย SUMIF ก็ได้นะ) แต่การเริ่มด้วย SUMIFS จะทำให้เพิ่มเงื่อนไขเพิ่มเติมได้ง่ายในภายหลัง โดยไม่ต้องเปลี่ยนโครงสร้างสูตรใหม่
.
ส่วนตัวผมแนะนำให้เริ่มด้วย SUMIFS เลยตั้งแต่แรก แม้ยังมีเงื่อนไขเดียวก็ตาม เพราะอนาคตถ้าต้องเพิ่มเงื่อนไขก็ไม่ต้องมานั่ง refactor สูตรอีกรอบ 😎
=SUMIFS(Sales[Amount], Sales[Region], "North")
850000
ตัวอย่างที่ 2: Practical – กรอง 2 เงื่อนไขพร้อมกัน (AND logic)
SUMIFS(Sales[Amount], Sales[Region], "North", Sales[Status], "Completed")
.
นี่คือ core use case ของ SUMIFS ที่ช่วยกรองข้อมูลหลายมิติในสูตรเดียว โดยไม่ต้องใช้ helper columns หรือซ้อนฟังก์ชันซับซ้อน
.
สังเกตว่าลำดับ arguments คือ sum_range ก่อน จากนั้นตามด้วยคู่ (criteria_range, criteria) ซ้ำกี่คู่ก็ได้ ถ้าจำไม่ได้ก็คิดง่ายๆ ว่า "บอกก่อนว่าจะบวกอะไร แล้วค่อยบอกว่าจะกรองยังไง" 💡
=SUMIFS(Sales[Amount], Sales[Region], "North", Sales[Status], "Completed")
650000
ตัวอย่างที่ 3: Date Range – กรองช่วงวันที่ด้วย DATE function
SUMIFS(Sales[Amount], Sales[Date], ">="&DATE(2024,1,1), Sales[Date], "
.
การใช้ DATE function ร่วมกับ & operator แบบนี้ (">="&DATE(2024,1,1)) ช่วยให้สร้าง date criteria แบบ dynamic ได้ โดยไม่ต้องพึ่ง hard-coded date strings ที่อาจเกิด format issues ตามภาษาของเครื่อง 💡
=SUMIFS(Sales[Amount], Sales[Date], ">="&DATE(2024,1,1), Sales[Date], "<="&DATE(2024,1,31))
320000
ตัวอย่างที่ 4: Wildcard – ค้นหาสินค้าด้วย pattern matching
SUMIFS(Products[Sales], Products[Name], "*Pro*", Products[Category], "Electronics")
.
การใช้ wildcard * ช่วยให้ไม่ต้อง list ชื่อสินค้าทีละตัว ซึ่งสะดวกมากเวลาต้องค้นหาแบบ pattern ตัวอักษร * แทน "ตัวอักษรกี่ตัวก็ได้" ส่วน ? แทน "ตัวอักษรเดียว" (เช่น "A???" = A ตามด้วย 3 ตัวอักษรใดๆ)
.
ข้อดีอีกอย่างคือ wildcards ทำงานแบบ case-insensitive คือ "pro" กับ "Pro" ให้ผลเหมือนกันเลย ไม่ต้องกังวลเรื่องตัวพิมพ์เล็ก-ใหญ่ 😎
=SUMIFS(Products[Sales], Products[Name], "*Pro*", Products[Category], "Electronics")
890000
ตัวอย่างที่ 5: Comparison Operators – มากกว่าและไม่เท่ากับ
SUMIFS(Orders[Total], Orders[Total], ">1000", Orders[Status], "Cancelled")
.
สังเกตว่า Orders[Total] ทำหน้าที่ทั้ง sum_range และ criteria_range ได้ (ตรวจสอบช่วงเดียวกันกับที่บวก) การใช้ <> หมายถึง "ไม่เท่ากับ" ซึ่งเป็นวิธีที่ดีในการ exclude ค่าบางอย่างออกจากผลรวม
.
เทคนิคนี้มีประโยชน์มากเวลาต้องการกรองข้อมูลที่มีคุณภาพ เช่น ตัดยอดที่ถูก cancel หรือยอดที่เล็กเกินไปออก ส่วนตัวผมใช้บ่อยเวลาทำรายงานยอดขายจริง 💡
=SUMIFS(Orders[Total], Orders[Total], ">1000", Orders[Status], "<>Cancelled")
1250000
ตัวอย่างที่ 6: Advanced – sum_range และ criteria_range เป็นช่วงเดียวกัน
SUMIFS(Sales[Commission], Sales[Commission], ">10000", Sales[Commission], "
.
นี่เป็นเทคนิคที่มีประโยชน์เมื่อต้องการกรองตัวเลขเฉพาะในช่วงที่กำหนด โดยไม่ต้องสร้าง helper column แยกเพื่อเช็คเงื่อนไข
.
ตอนแรกดูซับซ้อน แต่พอเข้าใจแล้วก็ใช้งานได้คล่องมาก ผมเคยใช้เทคนิคนี้กรองยอดขายที่อยู่ใน range เฉพาะเพื่อคำนวณ commission tier ต่างๆ ได้สบายเลย 😎
=SUMIFS(Sales[Commission], Sales[Commission], ">10000", Sales[Commission], "<50000")
180000
ตัวอย่างที่ 7: Dynamic Criteria – ใช้ cell references สำหรับ dashboard
SUMIFS(Sales[Amount], Sales[Region], G2, Sales[Year], H2, Sales[Quarter], I2)
.
เทคนิคนี้เหมาะสำหรับ interactive dashboards ที่ผู้ใช้เลือก filters ผ่าน dropdowns หรือ input cells แล้วสูตรคำนวณผลแบบ real-time โดยไม่ต้องแก้สูตร
.
ส่วนตัวผมใช้วิธีนี้ทำ dashboard ทุกครั้งเลย เพราะทำให้ end user สามารถ explore ข้อมูลเองได้โดยไม่ต้องมาขอให้แก้สูตร แค่เปลี่ยน dropdown ตัวเลขก็อัพเดททันที 💪
=SUMIFS(Sales[Amount], Sales[Region], G2, Sales[Year], H2, Sales[Quarter], I2)
425000
ตัวอย่างที่ 8: Exclude Empty – กรองเซลล์ว่างออก
SUMIFS(Sales[Amount], Sales[Region], "", Sales[Amount], ">0")
.
การใช้ "<>" เป็น criteria ช่วยตัดแถวที่มี empty cells หรือ blank values ออก ซึ่งเป็นเทคนิคสำคัญในการทำความสะอาดข้อมูลก่อนคำนวณ
.
เคยเจอปัญหาตัวเลขผิดเพราะมีแถวว่างปนมั้ยครับ? ผมเจอบ่อยมาก 😅 พอเริ่มใช้เทคนิคนี้ ปัญหาหายไปเลย ข้อมูลที่ไม่สมบูรณ์ก็ไม่มาทำให้ผลรวมผิดเพี้ยนอีกต่อไป
=SUMIFS(Sales[Amount], Sales[Region], "<>", Sales[Amount], ">0")
980000
ตัวอย่างที่ 9: Complex Real-World – Multi-dimensional sales analysis
SUMIFS(Transactions[Revenue], Transactions[Region], "North", Transactions[Product], "*Laptop*", Transactions[Date], ">="&DATE(2024,1,1), Transactions[Date], "5"…
.
สูตรนี้ใช้เงื่อนไข 5 ข้อพร้อมกัน: (1) Region filter, (2) Product wildcard search, (3-4) Date range, (5) Quantity threshold
.
นี่คือตัวอย่าง real-world ของการใช้ SUMIFS ในการวิเคราะห์ข้อมูลแบบ multi-dimensional filtering ที่ซับซ้อน โดยไม่ต้องพึ่ง PivotTable หรือ Power Query
.
ตอนแรกดูยาวน่ากลัว แต่ถ้าเขียนทีละคู่เงื่อนไข ก็ไม่ยากเลยครับ ผมเคยเขียนสูตรที่มี 7-8 เงื่อนไขสำหรับรายงานพิเศษ แล้วมันก็ยังทำงานได้ดีอยู่ 💪
=SUMIFS(Transactions[Revenue], Transactions[Region], "North", Transactions[Product], "*Laptop*", Transactions[Date], ">="&DATE(2024,1,1), Transactions[Date], "<="&DATE(2024,3,31), Transactions[Quantity], ">5")
2340000
FAQs
SUMIFS ต่างจาก SUMIF อย่างไร และควรใช้ตัวไหน?
ความแตกต่างหลัก 3 ข้อครับ:
.
(1) SUMIF รองรับเงื่อนไขเดียว, SUMIFS รองรับหลายเงื่อนไข (สูงสุด 127 คู่)
.
(2) ลำดับ arguments ต่างกัน: SUMIF ใช้ (range, criteria, sum_range) ส่วน SUMIFS ใช้ (sum_range, range1, criteria1, …) โดย sum_range มาก่อน
.
(3) SUMIFS ยืดหยุ่นกว่า เพราะแม้ใช้เงื่อนไขเดียวก็สามารถเพิ่มเงื่อนไขภายหลังได้ง่าย
.
ส่วนตัวผมแนะนำให้ใช้ SUMIFS เป็นหลักในโปรเจกต์ใหม่ เพื่อ consistency และเตรียมพร้อมสำหรับการขยาย criteria ในอนาคตครับ
ทำ OR logic (รวมค่าที่ตรงเงื่อนไขใดก็ได้) ด้วย SUMIFS ได้ไหม?
SUMIFS ทำได้เฉพาะ AND logic (ต้องตรงทุกเงื่อนไข) ถ้าต้องการ OR logic มี 2 วิธีครับ:
.
(1) รวม SUMIFS หลายตัวเข้าด้วยกัน เช่น =SUMIFS(Amount, Region, “North”) + SUMIFS(Amount, Region, “South”) เพื่อรวมยอดจาก North หรือ South
.
(2) ใช้ SUMPRODUCT กับ + operator สำหรับ OR เช่น =SUMPRODUCT(Amount * ((Region=”North”)+(Region=”South”)))
.
แต่ SUMPRODUCT ช้ากว่า SUMIFS มาก โดยเฉพาะข้อมูลเยอะๆ ดังนั้นถ้าทำได้ด้วย SUMIFS ควรใช้ SUMIFS เสมอครับ
.
ส่วนตัวผมใช้วิธีที่ 1 บ่อยกว่า เพราะอ่านง่ายและ maintain ได้ง่ายกว่า แม้สูตรจะยาวหน่อยก็ตาม 😅
ทำไมได้ #VALUE! error และจะแก้อย่างไร?
#VALUE! error เกิดจาก 3 สาเหตุหลักครับ:
.
(1) sum_range และ criteria_ranges มีขนาด (rows × columns) ไม่เท่ากัน → แก้โดยตรวจสอบให้ทุก range มี dimensions เท่ากัน
.
(2) ใช้ array แทน range เช่น YEAR(B5:B16) ซึ่ง SUMIFS ไม่รองรับ → แก้โดยใช้ helper column หรือเปลี่ยนไปใช้ SUMPRODUCT
.
(3) Reference ไปยัง external workbook ที่ปิดอยู่ → แก้โดยเปิด workbook นั้นหรือ copy data มาที่ workbook ปัจจุบัน
.
วิธีป้องกันที่ดีที่สุด: ใช้ structured references (Table[Column]) แทน cell ranges เพราะจะ auto-adjust และลด errors 💡
.
ผมเคยเจอ error แบบนี้เยอะมากตอนเริ่มใช้ SUMIFS พอรู้แล้วว่าสาเหตุคืออะไร ก็แก้ได้ง่ายขึ้นเยอะครับ
ช่วงเงื่อนไข (criteria_range) ต้องขนาดเท่ากันทุกตัวหรือไม่?
ใช่ครับ ทุก criteria_range ต้องมีขนาด (จำนวน rows และ columns) เท่ากับ sum_range ทุกประการ มิฉะนั้นจะได้ #VALUE! error
.
ตัวอย่าง: ถ้า sum_range คือ A2:A100 (99 rows × 1 column) ทุก criteria_range ต้องเป็น 99 rows × 1 column เช่น B2:B100, C2:C100 เป็นต้น
.
ข้อผิดพลาดที่เจอบ่อยคือการ reference ผิด row โดยไม่ตั้งใจ เช่น sum_range เป็น A2:A100 แต่ criteria_range เป็น B1:B100 (เยอะกว่า 1 แถว) 😅
.
วิธีป้องกัน: ใช้ Table structured references ซึ่งจะ auto-match ขนาดเสมอ
.
ผมเคยทำผิดแบบนี้เยอะเลย โดยเฉพาะตอนรีบๆ copy สูตร แล้วลืมปรับ range ให้ตรงกัน ตอนนี้เลยเปลี่ยนมาใช้ Table แทนเลย สบายกว่าเยอะ
ใช้ Wildcard อย่างไร และมีข้อจำกัดอะไรบ้าง?
SUMIFS รองรับ wildcard 3 แบบครับ:
.
(1) * แทนตัวอักษรกี่ตัวก็ได้ เช่น “*Pro*” = มีคำว่า Pro อยู่ตรงไหนก็ได้, “A*” = ขึ้นต้นด้วย A
.
(2) ? แทนตัวอักษรเดียว เช่น “A???” = A ตามด้วย 3 ตัวอักษรใดๆ
.
(3) ~ escape character สำหรับค้นหา * หรือ ? จริงๆ เช่น “~?” = เครื่องหมาย ? ตัวอักษร
.
ข้อจำกัด: Wildcards ทำงานเฉพาะกับ text criteria และเป็นแบบ case-insensitive (“pro” กับ “Pro” ให้ผลเหมือนกัน) ถ้าต้องการ case-sensitive search ต้องใช้ SUMPRODUCT กับ EXACT function แทน
.
ผมใช้ wildcard บ่อยมาก โดยเฉพาะตอน * เพราะช่วยให้ค้นหาได้ยืดหยุ่นโดยไม่ต้องระบุคำเต็ม 💡
SUMIFS เร็วกว่า SUMPRODUCT จริงหรือ และแตกต่างกันอย่างไร?
เร็วกว่ามากครับ SUMIFS ได้รับการ optimize เฉพาะสำหรับการกรองข้อมูลตาม criteria แบบ native โดย Excel engine ส่วน SUMPRODUCT ต้องคำนวณทุกเซลล์ก่อนรวม (evaluate entire arrays) ทำให้ช้ากว่าโดยเฉพาะกับข้อมูลขนาดใหญ่
.
ความแตกต่างหลัก:
.
(1) SUMIFS รองรับเฉพาะ AND logic, SUMPRODUCT ทำ OR และ complex logic ได้
.
(2) SUMIFS รองรับ wildcard, SUMPRODUCT ไม่รองรับโดยตรง
.
(3) SUMIFS ต้องใช้ range จริง, SUMPRODUCT ใช้ array functions ได้ เช่น YEAR()
.
กฎทอง: ถ้าทำได้ด้วย SUMIFS ให้ใช้ SUMIFS เสมอ
.
ผมเคยมีไฟล์ที่ช้ามาก พอเปลี่ยนจาก SUMPRODUCT มาเป็น SUMIFS ความเร็วดีขึ้นเห็นได้ชัดเลยครับ 😎
จะใช้ Date criteria ได้อย่างไร และมี best practices อะไร?
Date criteria มี 3 วิธีครับ:
.
(1) ใช้ DATE function กับ & operator เช่น “>=”&DATE(2024,1,1) (best practice – explicit และ locale-independent)
.
(2) ใช้ cell reference ที่เก็บ date เช่น “>=”&E10 (ดีสำหรับ dynamic criteria)
.
(3) Hard-code date string เช่น “>=2024-01-01” (ไม่แนะนำ – อาจเกิด format issues)
.
Best practices:
.
(1) ใช้ DATE function แทน hard-coded strings เพื่อหลีกเลี่ยงปัญหา regional date format
.
(2) สำหรับ date range ใช้ 2 เงื่อนไข: >= start date และ <= end date
.
(3) ใช้ EOMONTH, EDATE, TODAY, DATE ช่วยสร้าง dynamic date ranges
.
ผมแนะนำให้ใช้วิธีที่ 1 เสมอครับ เพราะไม่ว่าเครื่องจะตั้งค่าภาษาอะไร มันก็ทำงานได้ถูกต้องเสมอ ไม่ต้องกลัวเรื่อง date format ผิดเพี้ยน 💡
SUMIFS จัดการกับค่า TRUE/FALSE อย่างไร?
เมื่อ sum_range มีค่า TRUE/FALSE Excel จะแปลงเป็นตัวเลขก่อนบวก: TRUE = 1, FALSE = 0
.
ตัวอย่าง: =SUMIFS(BoolColumn, Criteria_Range, “Yes”) ถ้า BoolColumn มี TRUE 5 ตัว จะได้ผลรวม = 5
.
สำหรับ criteria: ค่า Boolean ใน criteria_range ก็แปลงเป็นตัวเลขเช่นกัน ดังนั้น criteria เช่น “>0” จะตรงกับ TRUE แต่ไม่ตรงกับ FALSE
.
ข้อควรระวัง: การแปลงนี้เกิด auto ทำให้อาจเกิดผลลัพธ์ที่ไม่คาดคิด แนะนำให้แปลง Boolean เป็น 1/0 อย่างชัดเจนผ่าน helper column หรือใช้ criteria แบบตรงตัว เช่น TRUE/FALSE แทน comparison
.
เคยเจอปัญหานี้มั้ยครับ? ผมเคยสับสนตอนเจอ TRUE/FALSE ในผลรวม พอรู้แล้วว่ามันทำงานยังไง ก็จัดการได้ง่ายขึ้นเยอะ 😅
Resources & Related
Resources
Related posts
Related functions
Additional Notes
SUMIFS เป็นฟังก์ชันคณิตศาสตร์ที่ใช้บวกค่าจาก sum_range เฉพาะแถวที่ตรงตามเงื่อนไขทุกข้อที่กำหนด (AND logic) รองรับได้สูงสุด 127 คู่เงื่อนไข ทำให้สามารถกรองข้อมูลหลายมิติพร้อมกันได้ในสูตรเดียว
.
ถ้าเคยใช้ SUMIF มาก่อน ต้องระวังเรื่องลำดับ argument นะครับ เพราะ SUMIFS นี่มัน “พลิกกลับ” ตรงที่ใส่ sum_range เป็นตัวแรกเลย ไม่ใช่ตัวท้ายแบบ SUMIF 😅
.
ตอนแรกๆ ที่เริ่มใช้ ผมก็สับสนเหมือนกัน พิมพ์ผิดลำดับบ่อยมาก 555 แต่พอคุ้นแล้วก็เข้าใจว่าทำไมถึงออกแบบแบบนี้ เพราะ sum_range ควรมาก่อนเพื่อให้เห็นว่าเรากำลังบวกอะไร
จุดเด่นของ SUMIFS คือทำ multi-dimensional filtering ได้สบายมาก ไม่ต้องมานั่งสร้าง helper columns หรือซ้อนฟังก์ชันซับซ้อน แค่เพิ่มคู่ criteria_range กับ criteria ไปเรื่อยๆ ก็ได้แล้ว
.
ส่วนตัวผมใช้ฟังก์ชันนี้บ่อยมากเวลาทำ dashboard เพราะสามารถ dynamic criteria ผ่าน cell references ได้ ทำให้ user เลือก filter แล้วเลขอัพเดทแบบ real-time เลย 😎
SUMIFS รองรับ comparison operators (>, =, <=, ) และ wildcard characters (* สำหรับหลายตัวอักษร, ? สำหรับตัวอักษรเดียว) ใน criteria ทำให้สามารถทำ text pattern matching และ numeric range filtering ได้อย่างยืดหยุ่น
.
นอกจากนี้ยังรองรับการใช้ DATE function ร่วมกับ & operator เพื่อสร้าง date criteria แบบ dynamic ซึ่งเป็นเทคนิคที่ผมใช้บ่อยมากเวลาทำรายงานประจำเดือน ไม่ต้องไปแก้วันที่ในสูตรให้ปวดหัว 💡