Ba công thức được sử dụng phổ biến nhất trong Excel để thực hiện các phép tính toán học đơn giản là COUNT , SUM và AVERAGE . Cho dù bạn đang quản lý ngân sách tài chính trong Excel hay chỉ đơn giản là theo dõi kỳ nghỉ tiếp theo của mình, bạn có thể đã sử dụng một trong những hàm này trước đây.

Trong bài viết này, chúng ta sẽ đi qua các khái niệm cơ bản về ba hàm này và các hàm tương ứng có liên quan và hữu ích của chúng: COUNTIFS , SUMIFS và AVERAGEIFS^{(SUMIFS and AVERAGEIFS)} .

Excel COUNT, SUM và AVERAGE

Để biết chúng ta đã bán được bao nhiêu điện thoại di động, chúng ta có thể nhanh chóng sử dụng công thức COUNT như hình dưới đây:

=COUNT(E2:E16)

Mặt khác, để có được tổng số tiền bán hàng mà chúng ta đã thực hiện, chúng ta có thể sử dụng công thức SUM như hình dưới đây:

=SUM(E2:E16)

Cuối cùng, để tìm ra doanh số bán hàng trung bình mà chúng tôi đã thực hiện cho tất cả các điện thoại, chúng tôi có thể sử dụng công thức AVERAGE như sau:

=AVERAGE(E2:E16)

Kết quả sẽ như sau:

Các công thức COUNT^(COUNT) , SUM và AVERAGE^{(SUM and AVERAGE formulas)} sẽ chỉ hoạt động đối với các bản ghi có giá trị ô^{(cell value)} ở định dạng số^{(number format)} . Bất kỳ bản ghi nào trong phạm vi công thức^{(formula range)} (ví dụ : E2: E16 trong ví dụ này) không ở định dạng số^{(number format)} sẽ bị bỏ qua.

Vì vậy, hãy đảm bảo rằng tất cả các ô trong công thức COUNT , SUM và AVERAGE^{(SUM and AVERAGE formula)} đều được định dạng là Số^(Number) , không phải Văn bản^(Text) . Cố gắng sử dụng cùng một công thức, nhưng với E: E là phạm vi thay vì E2: E16 . Nó sẽ trả về cùng một kết quả như trước vì nó bỏ qua tiêu đề (tức là Giá bán^{(Sale Price)} ), ở định dạng văn bản.

Bây giờ, điều gì sẽ xảy ra nếu chúng ta muốn biết số lần bán hàng, tổng số lần bán hàng và số lượng bán hàng trung bình trên mỗi chiếc điện thoại, chỉ đối với những chiếc điện thoại được bán ở Hoa Kỳ^(USA) ? Đây là nơi mà COUNTIFS , SUMIFS và AVERAGEIFS^{(SUMIFS and AVERAGEIFS)} đóng một vai trò quan trọng. Hãy quan sát công thức dưới đây:

COUNTIFS

Phân tích công thức:

1. =COUNTIFS( - Dấu “=” cho biết phần đầu của công thức trong ô và COUNTIFS là phần đầu tiên của hàm Excel^{(Excel function)} mà chúng tôi đang sử dụng.
2. D2: D16 - Đề cập^(Refers) đến phạm vi dữ liệu để kiểm tra xem liệu nó có đáp ứng các tiêu chí được đưa vào công thức đếm^{(count formula)} hay không .
3. “Hoa Kỳ”^(“USA”) - Tiêu chí^(Criteria) cần tìm trong phạm vi dữ liệu^{(data range)} được chỉ định ( D2: D16 )
4. ) - Dấu ngoặc^{(Closing bracket)} cho biết phần cuối của công thức.

Công thức trả về 6 là số lần bán hàng cho các sản phẩm được vận chuyển từ kho hàng của Hoa Kỳ^{(USA warehouse)} .

SUMIFS

Phân tích công thức:

1. =SUMIFS( - Dấu “=” cho biết lại phần bắt đầu của công thức.
2. E2: E16 - Đề cập^(Refers) đến phạm vi dữ liệu mà chúng tôi muốn tổng cộng, tức là giá^{(sale price)} ưu đãi trong ví dụ của chúng tôi.
3. D2: D16 - Đề cập^(Refers) đến phạm vi dữ liệu để kiểm tra xem liệu nó có đáp ứng các tiêu chí được đưa vào tổng số tiền hay không.
4. “Hoa Kỳ”^(“USA”) - Tiêu chí^(Criteria) cần tìm trong phạm vi dữ liệu^{(data range)} được chỉ định ( D2: D16 )
5. ) - Dấu ngoặc^{(Closing bracket)} cho biết phần cuối của công thức.

Công thức cho thấy tổng doanh thu $6,050 được thực hiện cho các sản phẩm được vận chuyển từ kho hàng của Hoa Kỳ^{(USA warehouse)} .

AVERAGEIFS

Phân tích công thức:

1. =AVERAGEIFS( - Dấu “=” cho biết phần đầu của công thức.
2. E2: E16 - Đề cập^(Refers) đến phạm vi dữ liệu mà chúng tôi muốn tính trung bình. Trong ví dụ này, chúng tôi muốn có được số lượng bán hàng trung bình cho tất cả các điện thoại được bán ở Hoa Kỳ^(USA) .
3. D2: D16 - Đề cập^(Refers) đến phạm vi dữ liệu để kiểm tra xem liệu nó có đáp ứng các tiêu chí được đưa vào công thức trung bình hay không.
4. “Hoa Kỳ”^(“USA”) - Tiêu chí^(Criteria) cần tìm trong phạm vi dữ liệu^{(data range)} được chỉ định
5. ) - Dấu ngoặc^{(Closing bracket)} cho biết các đầu của công thức.

Công thức cho thấy chúng tôi đã bán sản phẩm với giá khoảng $1,008 cho mỗi chiếc điện thoại ở Hoa Kỳ.

Cả ba công thức có thể có nhiều hơn một tiêu chí. Ví dụ: nếu chúng ta muốn biết các số liệu giống nhau (tức là COUNT , SUM và AVERAGE ) cho các sản phẩm được bán ở Hoa Kỳ^(USA) , nhưng chỉ cụ thể cho  thương hiệu Samsung , chúng ta chỉ cần thêm phạm vi dữ liệu cần kiểm tra theo tiêu chí của nó.

Vui lòng^(Please) xem ví dụ bên dưới nơi tiêu chí thứ hai được thêm vào các kiểm tra tiêu chí ban đầu. ( Văn bản màu xanh lam^{(Blue text)} cho biết tiêu chí đầu tiên và màu đỏ cho biết tiêu chí thứ hai)

=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")

Bạn sẽ nhận thấy rằng Excel cũng có  các công thức COUNTIF , SUMIF và AVERAGEIF không có hậu tố “S” . Chúng được sử dụng tương tự như COUNTIFS , SUMIFS và AVERAGEIFS . Tuy nhiên, những gì không có hậu tố “S” trong công thức có giới hạn là chỉ cho phép một tiêu chí cho mỗi công thức.

Vì cú pháp hơi khác, tôi khuyên bạn chỉ nên sử dụng  COUNTIFS , SUMIFS và AVERAGEIFS vì nó có thể được sử dụng cho một hoặc nhiều tiêu chí, nếu cần. Vui thích!

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

The three most сommonly used formulas in Excel that perfоrm simple mаthematical 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

• Thêm một Regression Trendline tuyến tính đến một Excel Scatter Plot

• Cách tạo Histogram trong Excel

• Cách an toàn Password Protect An Excel File

• Cách tạo Flowchart trong Word and Excel

• Cách Create Labels trong Word từ Excel Spreadsheet

• Sử dụng Excel Watch Window để theo dõi các ô quan trọng trong sổ làm việc

• Làm thế nào để Quay Macro trong Excel

• 13 OneNote Tips & Tricks Để tổ chức ghi chú của bạn tốt hơn

• Cách so sánh hai tệp Excel và làm nổi bật sự khác biệt

• Cách tính Variance bằng Excel

• Cách theo dõi các thay đổi trong Excel

• Sử dụng các hàm tóm tắt để tóm tắt dữ liệu trong Excel

• Cách chuyển đổi giữa các bảng tính trong Excel

• Hướng dẫn cho tất cả các phần mở rộng tệp Excel & Ý nghĩa của chúng

• Sử dụng Excel để tìm ra lãi suất hiệu quả từ lãi suất danh nghĩa

• Cách trừ ngày trong Excel

• Sử dụng Excel làm công cụ để sao chép dữ liệu từ web

• Cách tạo Greeting Card với MS Word

• Liên kết các ô giữa Trang tính và Sổ làm việc trong Excel

• Định dạng ô bằng cách sử dụng định dạng có điều kiện trong Excel