---
title: "วิธี SUM เฉพาะค่าที่เห็นจาก Filter แต่มีเงื่อนไข!"
url: https://www.thepexcel.com/sum-filter-with-criteria/
type: post
date: 2020-04-22
updated: 2025-12-22
author: Sira Ekabut
categories: [Excel ทั่วไป, Excel Advanced Formula]
tags: [SUMPRODUCT, SUMIFS, AGGREGATE, ROWS, INDIRECT, OFFSET, subtotal, SUM, ROW]
---

# วิธี SUM เฉพาะค่าที่เห็นจาก Filter แต่มีเงื่อนไข!

วันนี้เราจะมาดูวิธี SUM เฉพาะที่มองเห็นแบบมีเงื่อนไขกันครับ (Sum Filter with Criteria) ซึ่งเป็นเรื่องที่แปลกดี แต่ก็มีคนถามผมมา เลยเอามาตอบให้ทุกคนพร้อมกันเลย

 

หลายคนคงพอจะรู้อยู่บ้างว่า ถ้าเราใช้ฟังก์ชัน SUM ปกติ มันก็จะบวกข้อมูลตัวเลขแค่พื้นที่ในสูตรตรงนั้น **โดยไม่สนใจว่าเรา Filter อะไรอยู่หรือไม่**

 

สมมติข้อมูลก่อน Filter เป็นแบบนี้ ซึ่งรวมจำนวนชิ้นสินค้าทั้งหมดได้ 31 ชิ้น

 ![1](https://www.thepexcel.com/wp-content/uploads/2020/04/sum-filter-condition-001x3-1-1024x502.png) 

ถ้าเรา Filter ว่าเอาเฉพาะการชำระเงินเป็นเงินสด …จะพบว่า เจ้า SUM ก็จะได้เท่าเดิมอยู่ดี…

 

## ถ้าจะ SUM เฉพาะสิ่งที่ Filter เห็นอยู่

 

แต่ถ้าเราอยากจะ SUM แค่สิ่งที่ Filter อยู่เท่านั้น ต้องใช้ SUBTOTAL หรือไม่ก็ AGGREGATE มาช่วย เช่น

 

```
=SUBTOTAL(9,C5:C14)
```

 

เลข 9 ในสูตร เป็นการเลือกโหมดว่าจะให้ SUBTOTAL สรุปข้อมูลด้วยวิธีไหน? ซึ่ง 9 คือการ SUM นั่นเอง

 

```
=AGGREGATE(9,3,C5:C14)
```

 

ถ้าใช้ AGGREGATE เลข 9 ก็เหมือน SUBTOTAL แต่จะสามารถใส่ Option เพิ่มได้อีกว่าให้มันไม่สนใจอะไรบ้าง ซึ่งเลข 3 คือ ไม่สนแถวที่ซ่อนอยู่ ไม่สน error ไม่สน subtotal และ aggregate ที่ซ้อนอยู่ (พูดง่ายๆ คือ 3 ไม่สนมันทุกอย่างนี่แหละ)

 ![sum เฉพาะที่มองเห็นจาก filter แต่มีเงื่อนไข](https://www.thepexcel.com/wp-content/uploads/2020/04/sum-filter-condition-002-1024x423.png) 

## ถ้าไม่ Filter แต่จะ SUM เฉพาะอาหาร

 

ถ้าเราไม่ได้ Filter แต่จะ SUM แค่บางอย่าง แบบนี้เรียกว่า SUM แบบมีเงื่อนไข ซึ่งสามารถทำได้หลายแบบ เช่น

 

### ใช้ SUMIFS

 

```
=SUMIFS(C5:C14,B5:B14,"อาหาร")
```

 

ซึ่งแปลว่า ให้ SUM พื้นที่ C5:C14 โดยเงื่อนไขคือ B5:B14 เป็นคำว่าอาหาร

 

### ใช้ SUM แบบ Array

 

```
=SUM((C5:C14)*(B5:B14="อาหาร"))
```

 

หลักการทำงานคือหากลองลากครอบ B5:B14=”อาหาร” แล้วกด F9 จะได้ TRUE/FALSE แบบนี้

 

=SUM((C5:C14)*({TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}))

 

พอ TRUE/FALSE เชื่อมกับเครื่องหมายทางคณิตศาสตร์ มันก็จะกลายเป็น 1 กับ 0 ทำให้ ได้แบบนี้

 

```
=SUM({4;0;0;5;6;0;2;0;4;0})
```

 

ถ้าเป็น Excel ที่ไม่รองรับ Dynamic Array ต้องเขียนสูตรข้างบนแล้วกด Ctrl+Shift+Enter ด้วย จะมีปีกกางอกออกมา ถ้าไม่อยากต้องกด Ctrl+Shift+Enter ก็ให้ใช้ SUMPRODUCT แทน

 

### ใช้ SUMPRODUCT

 

```
=SUMPRODUCT((C5:C14)*(B5:B14="อาหาร"))
```

 ![2](https://www.thepexcel.com/wp-content/uploads/2020/04/sum-filter-condition-004-1024x412.png) 

## ถ้าจะ Filter เงินสด แต่จะ SUM เฉพาะอาหารล่ะ?

 

ฟังก์ชันพวก SUMIFS กับ SUMPRODUCT มันก็ดันไม่สนใจ Filter ด้วย เห็นมะว่าได้ 21 เท่าเดิมเลย

 ![3](https://www.thepexcel.com/wp-content/uploads/2020/04/sum-filter-condition-005-1024x363.png) 

### แล้วเราจะทำไงดี? ลองมั่วหลายๆ แบบดู

 

ถ้าเราเอา SUBTOTAL เขียนแบบ Array ล่ะ

 

```
=SUBTOTAL(9,(C5:C14)*(B5:B14="อาหาร"))
```

 

ปรากฏว่ามันไม่ยอมให้กด Enter ด้วยซ้ำ…

 

แล้วถ้าลองใช้ AGGREGATE ล่ะ เพราะมันก็สนใจเฉพาะตัวที่มองเห็นได้

 

```
=AGGREGATE(9,7,(C5:C14)*(B5:B14="อาหาร"))
```

 

ก็ดันขึ้น #VALUE! อีก เพราะฟังก์ชันหมายเลข 1-13 ดันไม่รองรับ Array…

 

แล้วถ้าเอา SUMPRODUCT ผสม SUBTOTAL ล่ะ!

 

```
=SUMPRODUCT(SUBTOTAL(9,C5:C14)*(B5:B14="อาหาร"))
```

 

คำตอบไม่ Error ด้วย แต่ออกมาได้ 135 ซึ่งผิดเห็นๆ

 

เพราะเกิดจาก =SUMPRODUCT(27*{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}) ซึ่งใช้ไม่ได้ เพราะมันดันคำนวณ SUBTOTAL ให้กลายเป็นค่าเดียวก่อนเลย แล้วค่อยมาคูณ กับ 1,0 ของ TRUE/FALSE

 

**ถ้าเราทำให้ SUBTOTAL มันคิดเลขทีละตัวล่ะ แบบนี้ก็ยังพอมีความหวัง!**

 

## Solution

 

### วิธีแบบง่ายๆ

 

วิธีที่ง่ายคือ เพิ่มคอลัมน์พิเศษที่เอาไว้ดักการ Filter ซะ แล้วเขียนสูตรให้เป็น 1,0 โดยให้เป็น 1 เมื่อมองเห็น ดังนี้

 

```
=SUBTOTAL(3,A5)
```

 

ผมเลือก COUNTA ไปที่ช่อง A5 เพราะคิดว่าช่องนั้นยังไงก็ต้องมีค่าเสมอ ไม่ใช่ช่องว่าง

 ![4](https://www.thepexcel.com/wp-content/uploads/2020/04/sum-filter-condition-006.png) 

แบบนี้เราก็จะใช้ SUMIFS หรือ SUMPRODUCT ได้แล้ว

 

```
=SUMIFS(C5:C14,B5:B14,"อาหาร",E5:E14,1)
แค่เพิ่มคอลัมน์พิเศษว่าต้องเป็น 1
```

 

```
=SUMPRODUCT(C5:C14*(B5:B14="อาหาร")*E5:E14)
แค่คูณคอลัมน์พิเศษเข้าไป
```

 ![5](https://www.thepexcel.com/wp-content/uploads/2020/04/sum-filter-condition-008-1024x323.png) 

### แต่ถ้าไม่เพิมคอลัมน์เอา ก็จะอยากขึ้นเยอะเลย!!

 

ผมไปเจอในเว็บ ExcelJet ทำไว้ หลักการคือเค้าใช้ OFFSET ช่วยดึงค่ามาทีละช่องแล้วค่อยส่งให้ SUBTOTAL ทำงานต่อ ซึ่งใครสนใจก็ลองเข้าไปดูได้ครับ [https://exceljet.net/formula/count-visible-rows-only-with-critera](https://exceljet.net/formula/count-visible-rows-only-with-criteria)

 

ถ้าเป็นผมทำเองด้วยหลักการคล้ายๆ กันก็จะได้สูตรแบบนี้

 

```
=SUMPRODUCT((B5:B14="อาหาร")*SUBTOTAL(9,OFFSET(C4,ROW(INDIRECT("1:"&ROWS(C5:C14))),0)))
```

 

ซึ่งยากกว่าการเพิ่มคอลัมน์พิเศษเยอะเลยเนอะ

 

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

---

_Source: [https://www.thepexcel.com/sum-filter-with-criteria/](https://www.thepexcel.com/sum-filter-with-criteria/)_