สูตรที่ใช้บ่อยที่สุดสามสูตรในExcelที่ทำการคำนวณทางคณิตศาสตร์อย่างง่ายได้แก่COUNT , SUMและAVERAGE ไม่ว่าคุณจะจัดการงบประมาณทางการเงินในExcelหรือเพียงแค่ติดตามวันหยุดพักผ่อนครั้งต่อไปของคุณ คุณอาจเคยใช้ฟังก์ชันใดฟังก์ชันหนึ่งเหล่านี้มาก่อน

ในบทความนี้ เราจะพูดถึงพื้นฐานของฟังก์ชันทั้งสามนี้และคู่ที่เกี่ยวข้องและมีประโยชน์: COUNTIFS , SUMIFS และ^{(SUMIFS and AVERAGEIFS)} AVERAGEIFS

Excel COUNT, SUM และ AVERAGE

หากต้องการทราบจำนวนโทรศัพท์มือถือที่เราขาย เราสามารถใช้สูตรCOUNT ได้อย่างรวดเร็วดังแสดงด้านล่าง:^(COUNT)

=COUNT(E2:E16)

ในทางกลับกัน เพื่อให้ได้ยอดรวมของยอดขายที่เราทำ เราสามารถใช้สูตรSUM ดังแสดงด้านล่าง:^(SUM)

=SUM(E2:E16)

สุดท้าย เพื่อหายอดขายเฉลี่ยที่เราทำสำหรับโทรศัพท์ทุกรุ่น เราสามารถใช้ สูตร AVERAGEดังต่อไปนี้:

=AVERAGE(E2:E16)

ผลลัพธ์ควรเป็นดังนี้:

สูตร COUNT^(COUNT) , SUM และ AVERAGE^{(SUM and AVERAGE formulas)}จะใช้ได้เฉพาะกับระเบียนที่ค่าของเซลล์^{(cell value)}อยู่ใน รูป แบบตัวเลข ^{(number format)}บันทึกใดๆ ภายในช่วงสูตร^{(formula range)} (เช่นE2:E16ในตัวอย่างนี้) ที่ไม่อยู่ในรูปแบบตัวเลข^{(number format)}จะถูกละเว้น

ดังนั้น โปรดตรวจสอบให้แน่ใจว่าทุกเซลล์ใน สูตร COUNT , SUM และ AVERAGE^{(SUM and AVERAGE formula)}มีรูปแบบเป็นNumberไม่ใช่Text ลองใช้สูตรเดียวกัน แต่E:Eเป็น range แทนE2:^(E2:E16) E16 มันจะส่งคืนผลลัพธ์เหมือนเดิมเพราะไม่สนใจส่วนหัว (เช่นSale Price ) ซึ่งอยู่ในรูปแบบข้อความ

แล้วถ้าเราต้องการทราบจำนวนการขาย ยอดรวมของการขาย และจำนวนเฉลี่ยของการขายต่อโทรศัพท์หนึ่งเครื่อง เฉพาะที่ขายในสหรัฐอเมริกา^(USA)เท่านั้น นี่คือจุดที่COUNTIFS , SUMIFS และ AVERAGEIFS^{(SUMIFS and AVERAGEIFS)}มีบทบาทสำคัญ สังเกตสูตรด้านล่าง:

COUNTIFS

รายละเอียดสูตร:

1. =COUNTIFS( – “=”หมายถึงจุดเริ่มต้นของสูตรในเซลล์ และCOUNTIFSคือส่วนแรกของฟังก์ชัน Excel^{(Excel function)}ที่เราใช้
2. D2:D16 – หมายถึง^(Refers)ช่วงของข้อมูลเพื่อตรวจสอบเพื่อดูว่าเป็นไปตามเกณฑ์ที่จะรวมอยู่ในสูตรการนับ^{(count formula)}หรือไม่
3. “สหรัฐอเมริกา”^(“USA”) – เกณฑ์^(Criteria)การค้นหาในช่วงข้อมูล^{(data range)}ที่ระบุ ( D2:D16 )
4. ) – วงเล็บปิด^{(Closing bracket)}ระบุจุดสิ้นสุดของสูตร

สูตรส่งคืน 6 ซึ่งเป็นจำนวนการขายสำหรับผลิตภัณฑ์ที่จัดส่งจากคลังสินค้า^{(USA warehouse)} ใน สหรัฐอเมริกา

SUMIFS

รายละเอียดสูตร:

1. =SUMIFS( – “=”หมายถึงจุดเริ่มต้นของสูตรอีกครั้ง
2. E2:E16 – หมายถึง^(Refers)ช่วงของข้อมูลที่เราต้องการรวม เช่นราคาขาย^{(sale price)}ในตัวอย่างของเรา
3. D2:D16 – หมายถึง^(Refers)ช่วงของข้อมูลเพื่อตรวจสอบเพื่อดูว่าตรงตามเกณฑ์ที่จะรวมในจำนวนเงินทั้งหมดหรือไม่
4. “สหรัฐอเมริกา”^(“USA”) – เกณฑ์^(Criteria)การค้นหาในช่วงข้อมูล^{(data range)}ที่ระบุ ( D2:D16 )
5. ) – วงเล็บปิด^{(Closing bracket)}ระบุจุดสิ้นสุดของสูตร

สูตรนี้แสดง ยอดขายรวม $6,050สำหรับผลิตภัณฑ์ที่จัดส่งจากคลังสินค้า^{(USA warehouse)} ใน สหรัฐอเมริกา

ค่าเฉลี่ย^(AVERAGEIFS)

รายละเอียดสูตร:

1. =AVERAGEIFS( – “=”หมายถึงจุดเริ่มต้นของสูตร
2. E2:E16 – หมายถึง^(Refers)ช่วงของข้อมูลที่เราต้องการหาค่าเฉลี่ย ในตัวอย่างนี้ เราต้องการรับยอดขายเฉลี่ยสำหรับโทรศัพท์ทุกเครื่องที่จำหน่ายในสหรัฐอเมริกา^(USA)
3. D2:D16 – หมายถึง^(Refers)ช่วงของข้อมูลเพื่อตรวจสอบเพื่อดูว่าเป็นไปตามเกณฑ์ที่จะรวมไว้ในสูตรเฉลี่ยหรือไม่
4. “สหรัฐอเมริกา”^(“USA”) – เกณฑ์^(Criteria)การค้นหาในช่วงข้อมูล^{(data range)}ที่ระบุ
5. ) – วงเล็บปิด^{(Closing bracket)}ระบุจุดสิ้นสุดของสูตร

สูตรนี้แสดงว่าเราขายผลิตภัณฑ์นั้นในราคาประมาณ$1,008ต่อโทรศัพท์หนึ่งเครื่องในสหรัฐอเมริกา

ทั้งสามสูตรสามารถมีได้มากกว่าหนึ่งเกณฑ์ ตัวอย่างเช่น หากเราต้องการทราบตัวเลขเดียวกัน (เช่นCOUNT , SUMและAVERAGE ) สำหรับผลิตภัณฑ์ที่ขายในสหรัฐอเมริกา^(USA)แต่เฉพาะสำหรับ  แบรนด์ Samsungเท่านั้น เราเพียงแค่ต้องเพิ่มช่วงข้อมูลที่จะตรวจสอบตามเกณฑ์

โปรด^(Please)ดูตัวอย่างด้านล่างซึ่งมีการเพิ่มเกณฑ์ที่สองลงในการตรวจสอบเกณฑ์เริ่มต้น ( ข้อความสีน้ำเงิน^{(Blue text)}ระบุเกณฑ์แรก และสีแดงระบุเกณฑ์ที่สอง)

=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung")

=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

คุณจะสังเกตเห็นว่าExcelมี  สูตร COUNTIF , SUMIFและAVERAGEIFโดยไม่มีคำต่อท้าย“S ” สิ่งเหล่านี้ถูกใช้คล้ายกับCOUNTIFS , SUMIFSและAVERAGEIFS อย่างไรก็ตาม ผู้ที่ไม่มีคำต่อท้าย“S”ในสูตรมีข้อจำกัดในการอนุญาตเพียงหนึ่งเกณฑ์ต่อสูตรเท่านั้น

เนื่องจากไวยากรณ์แตกต่างกันเล็กน้อย ฉันจึงขอแนะนำให้ใช้  COUNTIFS , SUMIFSและAVERAGEIFSเท่านั้น เนื่องจากสามารถใช้ได้กับเกณฑ์อย่างใดอย่างหนึ่งหรือมากกว่า หากจำเป็น สนุก!

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

The three most commonly used formulas in Excel that perform simplе mathematical calcυlations are COUNT, SUM and AVERAGE. Whether you are managing a financial budget in Excel or simply keeping track of your next vacation, you’ve probably used one of these functions before.

In this article, we’re going to go through the basics of these three functions and their relevant and useful counterparts: COUNTIFS, SUMIFS and AVERAGEIFS.

Excel COUNT, SUM and AVERAGE

To know how many mobile phones that we have sold, we can quickly use the COUNT formula as shown below:

=COUNT(E2:E16)

On the other hand, to get the total amount of sales that we have made, we can use the SUM formula as shown below:

=SUM(E2:E16)

Lastly, to find out the average sales that we made for all phones, we can use the AVERAGE formula as below:

=AVERAGE(E2:E16)

The result should be as below:

COUNT, SUM and AVERAGE formulas will only work for records where the cell value is in number format. Any record within the formula range (i.e. E2:E16 in this example) not in the number format will be ignored.

So, please ensure that all cells within the COUNT, SUM and AVERAGE formula are all formatted as Number, not Text. Try to use the same formula, but with E:E as the range instead of E2:E16. It will return the same result as before because it ignores the header (i.e. Sale Price), which is in text format.

Now, what if we want to know number of sales, total amount of sales and the average amount of sales per phone, just for those sold in USA? This is where COUNTIFS, SUMIFS and AVERAGEIFS play an important role. Observe the formula below:

COUNTIFS

Formula breakdown:

1. =COUNTIFS( – The “=” indicates the beginning of a formula in the cell and COUNTIFS is the first part of the Excel function that we are using.
2. D2:D16 – Refers to range of data to check to see if it satisfies the criteria to be included in the count formula.
3. “USA” – Criteria to look for in the data range specified (D2:D16)
4. ) – Closing bracket indicating the end of the formula.

The formula returns 6 which is the number of sales for products shipped from the USA warehouse.

SUMIFS

Formula breakdown:

1. =SUMIFS( – The “=” indicates the beginning of the formula again.
2. E2:E16 – Refers to range of data that we would like to total, i.e. sale price in our example.
3. D2:D16 – Refers to range of data to check to see if it satisfies the criteria to be included in the total amount.
4. “USA” – Criteria to look for in the data range specified (D2:D16)
5. ) – Closing bracket indicating the end of the formula.

The formula shows $6,050 total sales that were made for products shipped from the USA warehouse.

AVERAGEIFS

Formula breakdown:

1. =AVERAGEIFS( – The “=” indicate the beginning of formula.
2. E2:E16 – Refers to range of data that we would like to average. In this example, we want to get the average amount of sales for all phones sold in the USA.
3. D2:D16 – Refers to range of data to check to see if it satisfies the criteria to be included in the average formula.
4. “USA” – Criteria to look for in the data range specified
5. ) – Closing bracket indicating the ends of the formula.

The formula shows we sold the product for around $1,008 per phone in USA.

All three formula can take more than one criteria. For example if we want to know the same figures (i.e. COUNT, SUM and AVERAGE) for products sold in USA, but specifically only for the Samsung brand, we just need to add the data range to be checked followed by its criteria.

Please see example below where a second criteria is added to the initial criteria checks. (Blue text indicates the first criteria and red indicates the second criteria)

=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung")

=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

You will notice that Excel also has COUNTIF, SUMIF and AVERAGEIF formulas without the suffix “S”. Those are used similar to COUNTIFS, SUMIFS and AVERAGEIFS. However, those without the suffix “S” in the formula have the limitation of only allowing one criteria per formula.

As the syntax is slightly different, I would recommend using COUNTIFS, SUMIFS and AVERAGEIFS only as it can be used for either one criteria or more, if necessary. Enjoy!

Related posts

• เพิ่ม Regression Trendline แนวตรงไปยัง Excel Scatter Plot

• วิธีทำ Histogram ใน Excel

• วิธีที่จะปลอดภัย Password Protect Excel File

• วิธีสร้าง Flowchart ใน Word and Excel

• วิธีการ Create Labels ใน Word จาก Excel Spreadsheet

• วิธีเพิ่มความคิดเห็นในเซลล์แผ่นงาน Excel

• วิธีทำความเข้าใจการวิเคราะห์แบบ What-If ใน Microsoft Excel

• วิธีติดตามการเปลี่ยนแปลงใน Excel

• วิธีลบ แตก หรือทำลายรหัสผ่าน Excel XLS ที่ถูกลืม

• วิธีใช้ VLOOKUP ใน Excel

• วิธีเปรียบเทียบไฟล์ Excel สองไฟล์และเน้นความแตกต่าง

• แทรกแผ่นงาน Excel ลงใน Word Doc

• วิธีซ่อนชีต เซลล์ คอลัมน์ และสูตรใน Excel

• ใช้ฟังก์ชันสรุปเพื่อสรุปข้อมูลใน Excel

• วิธีการ Automatically Backup Word Document เพื่อวันไดรฟ์

• วิธีสลับระหว่างแผ่นงานใน Excel

• ใช้หน้าต่างดูของ Excel เพื่อตรวจสอบเซลล์ที่สำคัญในสมุดงาน

• วิธีค้นหาค่าที่ตรงกันใน Excel

• ใช้ Excel เพื่อหาอัตราดอกเบี้ยที่แท้จริงจากอัตราดอกเบี้ยที่กำหนด

• วิธีเพิ่มแถบข้อผิดพลาดใน Excel