Nhắc tới đếm theo không ít điều kiện chúng ta thường nghĩ về tới hàm COUNTIFS. Nhưng các bạn có biết rằng SUMPRODUCT cũng làm được điều này? Hãy thuộc Học Excel Online tò mò cách dùng hàm SUMPRODUCT nhằm đếm theo rất nhiều điều khiếu nại nhé:


Nguyên tắc đếm của hàm SUMPRODUCT

Hàm SUMPRODUCT là 1 trong những hàm xử lý tài liệu dạng mảng. Cấu tạo của SUMPRODUCT dễ dàng và đơn giản chỉ là các mảng:

=SUMPRODUCT(array1, , , …)

Nếu chỉ quan sát vào kết cấu trên thì bạn có tưởng tượng được hàm SUMPRODUCT có sức khỏe thế như thế nào không? sức mạnh nằm sinh hoạt chữ “”Mảng-Array”, vị vì:

Phạm vi của mảng rất linh thiêng hoạt, có thể gồm nhiều vùng dữ liệu được liên kết lại hoặc một trong những phần trong mỗi bộ phận của 1 vùng dữ liệu.Việc tính toán theo mảng gần như không chạm chán giới hạn gì

Vậy hàm SUMPRODUCT tiến hành việc đếm dựa vào nguyên tắc nào?

Bản chất của bài toán đếm là mỗi thành phần trong 1 vùng thỏa mãn nhu cầu điều khiếu nại thì được tính là 1. Tác dụng cần đếm là tổng của các giá trị vừa lòng điều khiếu nại đó. Vậy nên trong bài toán đếm theo điều kiện có thể hiểu gần giống tính tổng các giá trị vừa lòng điều kiện, chỉ khác là mỗi giá trị vừa lòng này không phụ thuộc 1 quý hiếm nào mà chỉ có giá trị là 1.

Bạn đang xem: Hàm sumproduct tính tổng nhiều điều kiện

Ứng dụng hàm SUMPRODUCT đếm theo rất nhiều điều kiện

Dưới đấy là một số ví dụ nhằm giúp họ làm thân quen với bài toán dùng hàm SUMPRODUCT nhằm đếm:


*

*

Yêu mong thứ 1: phụ thuộc vào bảng dữ liệu A1:D18 đếm số sản phẩm Cam bán trong tháng 6

Trong yêu thương cầu này có 2 đk đếm:

Mặt hàng là Cam. Xét vào cột Tên mặt hàng với điều kiện “Cam”Tháng 6. Xét trong cột Ngày, mỗi thành phần (ô) trong cột ngày chỉ xét quý hiếm tháng bằng 6

Như vậy mỗi giá trị thỏa mãn cả 2 điều kiện này thì được tính là 1. Tác dụng là tổng những giá trị thỏa mãn cả 2 đk này.

Xét đk thứ 1:

Theo phương pháp viết thông thường thì bọn họ viết

C2=”Cam”

C3=”Cam”

…

C18=”Cam”

Khi viết dưới đạng mảng thì chúng ta có thể viết gọn là C2:C18=”Cam”. Khi viết như trên thì bọn họ hiểu là xét mỗi quý hiếm trong vùng C2:C18, ô nào có mức giá trị là Cam.

Xét đk thứ 2:

Để xét tháng của một Ngày thì bọn họ sử dụng hàm MONTH. Để xét mỗi cực hiếm trong cột Ngày bao gồm tháng bởi 6 thì chúng ta có thể viết như sau:

MONTH(A2:A18)=6

Hàm MONTH thông thường chỉ xét được trên 1 giá trị. Vì thế nếu viết thường thì thì chỉ viết dạng:

Month(A2)=6

Month(A3)=6

…

Month(A18)=6

Khi viết dưới dạng cách làm mảng thì có thể viết Month(A2:A18)=6

Do hàm Sumproduct là hàm dạng mảng nên chúng ta cũng có thể sử dụng trực tiếp bí quyết viết rút gọn này.

Hai điều kiện trên là đồng thời, nên trong hàm SUMPRODUCT có thể viết như sau:

=SUMPRODUCT((C2:C18=”Cam”)*(MONTH(A2:A18)=6)*1)

Mỗi đk được viết trong dấu ngoặc đơn, link với nhau vì chưng dấu *.

Kết quả của các điều kiện đã trả về TRUE hoặc FALSE chứ chưa hẳn bằng 1. Vì thế phải nhân thêm giá trị 1 để quy tác dụng về dạng số để hoàn toàn có thể tính tổng được.

Kết đúng như sau:

*

(Các vùng tham chiếu A2:A18, C2:C18 có thể cố định và thắt chặt lại bằng phím F4)

Yêu mong 2: Đếm số lần phân phối của ca 1 vào thời điểm tháng 6 với sản phẩm Táo

Trong yêu cầu này còn có tới 3 điều kiện: Ca1, mon 6 và món đồ “Táo”

Khi tăng thêm điều kiện thì bọn họ chỉ cần tăng lên các thành phía bên trong hàm SUMPRODUCT như sau:

=SUMPRODUCT(($B$2:$B$18=1)*(MONTH($A$2:$A$18)=6)*($C$2:$C$18=”Táo”)*1)

Kết quả:

*

Như vậy bài toán thêm điều kiện thực tế chỉ có tác dụng hàm SUMPRODUCT nhiều năm hơn, còn ý nghĩa sâu sắc và độ khó không hề tăng lên. Khi vẫn hiểu rõ phương thức và cách áp dụng hàm SUMPRODUCT vào việc đếm theo khá nhiều điều kiện thì chúng ta sẽ thấy tính ứng dụng của câu hỏi này không nhỏ trong thực tế.

4 cách thức đếm nhiều điều kiện dạng hoặc – Hàm SUMPRODUCT

Ngoài ra học tập Excel Online xin trình làng với những bạn Khóa học Excel trường đoản cú cơ bạn dạng tới cải thiện dành đến người đi làm. Đây là 1 trong khóa học tập rất đầy đủ kiến thức và kỹ năng và hỗ trợ rất tuyệt vời cho chính mình trong vấn đề làm quen với Excel, sử dụng những công cụ, những hàm trong Excel để giúp bạn thao tác trên Excel được xuất sắc hơn, công dụng hơn. Cụ thể xem tại:

Excel là ứng dụng cung ứng làm vấn đề văn phòng cực kỳ hiệu quả. Trong excel có cung ứng nhiều hàm góp việc đo lường và tính toán trở nên đơn giản và dễ dàng hơn, một trong những đó phải kể tới hàm Sumproduct. Vậy hàm sumproduct để triển khai gì?. Bài viết dưới phía trên sẽ hỗ trợ những thông tin có ích về các loại hàm này với hướng dẫn bạn cách cần sử dụng hàm sumproduct tính tổng các điều kiện

*
Sumproduct là hàm gì trong Excel?

Tìm đọc về hàm Sumproduct là gì?

Hàm sumproduct là một trong những hàm cách làm trong excel ở trong nhóm dữ liệu dạng mảng. Đây là sự kết hợp của hai hàm sum – hàm tính tổng và hàm hàng hóa – hàm cần sử dụng tính tích.

Cú pháp sử dụng hàm: =sumproduct (array1, , ,…)

Trong đó:

array1 là thông số bắt buộc. Đây đó là đối số của mảng vật dụng nhất, tại đây chứa các thành phần mà bạn muốn nhân, tiếp đến cộng tổng chúng lại cùng với nhau., …chính là thông số tuỳ chọn. Từ mảng thiết bị hai trở đi là những mảng mà bạn có nhu cầu nhân các thành phía bên trong nó rồi cộng bọn chúng lại với nhau.

Nguyên tắc đếm của hàm sumproduct trong excel

Giá trị của hàm sumproduct đến từ các mảng array trong bí quyết tính của nó. Phạm vi hoạt động của các mảng này khá linh hoạt, chúng ta có thể gộp nhiều vùng tài liệu vào hoặc một vài thành phần của một vùng dữ liệu. Việc giám sát theo những mảng đang không chạm mặt giới hạn, việc đo lường trở nên tiện lợi hơn.

Để đạt điều kiện thì mỗi thành phần trong vung kia phải thỏa mãn điều khiếu nại thì sẽ được tính là 1. Công dụng của mỗi lần đếm chuyển ra đó là tổng các giá trị thỏa mãn nhu cầu những điều kiện trên. Câu hỏi này tương tự như với việc tính tổng những giá trị vừa lòng điều khiếu nại mà bọn họ hay dùng. Mặc dù điểm khác hoàn toàn chính là mỗi giá chỉ trị vừa lòng sẽ không nhờ vào vào bất kỳ giá trị nào khác bên cạnh 1.

Những chú ý khi áp dụng hàm sumproduct

Các mảng vào hàm sumproduct đều có cùng số cột, số hàng. Nếu như không cùng số cột số hàng, lúc tính công dụng sẽ trả về cực hiếm lỗi, các bạn sẽ không thể tiến hành được phép tính.Trường hợp tất cả một ô nào kia trong mảng không tồn tại dạng số, hàm sumproduct trường đoản cú mặc định giá trị của chính nó là 0 và tiếp tục tính toán.Số mảng về tối đa rất có thể tính toán bởi hàm sumproduct là 255 mảng với các đời excel tự 2007 trở đi.Hàm này không cung ứng ký từ bỏ đại diện.Nếu mảng là một trong phép test logic, quý hiếm trả về đã là FALSE hoặc TRUE.

Hướng dẫn sử dụng hàm sumproduct tính tổng trong excel

Có vô số cách thức dùng hàm sumproduct tính tổng vào excel. Sau đây chính là hướng dẫn cách dùng hàm trong một vài trường đúng theo thường gặp.

Dùng hàm sumproduct tính tổng của những tích

Đây là phần sử dụng cơ bản của hàm này, chúng ta cũng có thể đơn giản phép tính của mình mà hiệu quả trả về trọn vẹn chính xác. Điều này giúp tiết kiệm thời gian, sức lực lao động của các bạn trong làm việc.

Ví dụ 1: tính tổng lệch giá của một cửa hàng có chào bán nhiều một số loại mặt hàng. Nếu khách hàng không biết cách sử dụng hàm sumproduct vào excel thì phép tính của các bạn sẽ dài hơn. Bạn phải tính tích từng cột riêng kế tiếp lại gộp tổng của chúng lại. Hàm sumproduct sẽ hỗ trợ giải quyết rất đối chọi giản:

Nhập cú pháp: =SUMPRODUCT (A2:A5,B2:B5) và enter, bạn sẽ nhận được công dụng trả về chính xác.

*
Dùng hàm sumproduct tính tổng của các tích

Dùng hàm sumproduct tính tổng những điều kiện

Đây chính là điểm mạnh của hàm này, giúp hỗ trợ tính toán rất nhiều trường hợp khó khăn hơn với rất nhiều mảng tinh vi hơn. Là một nhân viên văn phòng, nhất là kế toán, bài toán nắm vững các hàm giám sát và đo lường trong excel là vấn đề cần thiết. Điều này giúp các bước của bạn trở nên thuận buồm xuôi gió hơn, các bạn không còn lo ngại khi phải đo lường quá những số liệu khác nhau. Hãy coi và vận dụng nó trong các bước nhé, kiên cố chắn bạn sẽ có kết quả làm việc xuất sắc hơn.

Ví dụ: các bạn hãy tính tổng những Sp1 thuộc miền nam theo tài liệu trong bảng bên dưới đây.

*
Dùng hàm sumproduct nhiều điều kiện

Chúng ta thuộc phân tích đề bài xích để làm rõ hơn nhé. Theo yêu thương cầu, ta nên tính tổng Sp1 cùng với hai đk phải đáp ứng nhu cầu đó là sản phẩm đó thuộc khu vực miền nam và có tên là Sp1.

Cách tính việc này được trình bài theo công việc sau:

Bước 1: Khi khẳng định được các điều khiếu nại rõ ràng, ta hợp tác vào tính toán. Với đk tên thành phầm là sp1, quan sát vào bảng ta thấy được không những một mà có tương đối nhiều mã hàng tất cả chứ Sp1. Bây giờ, ta phải tách bóc sp1 ra khỏi những mã hàng đó bằng cách dùng cách làm sau: =LEFT(A2:A7,3). Trong công thức này, 3 đó là số ký kết tự cần lấy, A2: A7 đó là mảng tên.

Bước 2: Với điều kiện miền Nam, bạn phải bóc chúng thoát khỏi mảng những miền. Thực hiện công thức sau để tách: =RIGHT(B1:E1, 3).

Bước 3: Khi sẽ xử lý bóc tách điều khiếu nại xong, bây chừ ta sử dụng hàm sumproduct để giám sát và đo lường kết quả.

Ta nhập bí quyết sau: =SUMPRODUCT ((LEFT(A2:A7, 3)= “SP1”)*RIGHT(B1:(B2:E7)).

Sau đó dìm enter cùng ta thu được kết quả đúng chuẩn cần tính.

*
Kết quả cần sử dụng hàm sumproduct vào Excel 2007 nhằm tính tổng

Một số lấy ví dụ về hàm Sumproduct nâng cao

Hàm SUMPRODUCT linh hoạt cùng nhiều công dụng hơn rất đôi khi tính tổng theo điều kiện. Dưới đó là một số hàm Sumproduct tính tổng nhiều đk nâng cao bạn có thể tham khảo

Tính hàm sumproduct gồm điều kiện

Ví dụ:

*
Ví dụ hàm Sumproduct có điều kiện

Cách sử dụng hàm SUMPRODUCT có điều kiện ở trên:

Biểu thức (C4:C8>140): thực hiện so sánh từng bộ phận của mảng cùng với 140.Sau lúc so sánh xong xuôi các thành phần trong mảng, hàm SUMPRODUCT trở thành: SUMPRODUCT(- FALSE, FALSE, TRUE, TRUE, FALSE)Dấu âm “ – ” đặt trước mảng để biến các giá trị TRUE/ FALSE về dạng số nguyên -1 cùng 0 thì kết quả hàm SUMPRODUCT mới trả về số chủ yếu xác, nếu như không hàm vẫn trả về 0. Hoàn toàn có thể sử dụng 2 vết “-” để trả TRUE/ FALSE về 1 với 0.

Xem thêm: Tải Và Chơi Candy Crush Soda Saga Trên Máy Tính Windows 10, Tải Candy Crush Soda Saga Trên Pc Với Giả Lập

Sử dụng hàm SUMPRODUCT nhiều điều kiện

Ví dụ này áp dụng hàm SUMPRODUCT nhiều điều kiện thay mang lại hàm COUNTIFS vẫn thường sử dụng

*
Ví dụ hàm Sumproduct những điều kiện

Giải thích cách áp dụng hàm SUMPRODUCT nhiều điều kiện:

Biểu thức (A4:A8=”táo”): triển khai kiểm tra từng bộ phận của mảng liệu có phải là Táo tốt không: tác dụng TRUE, TRUE, FALSE, FALSE, TRUEBiểu thức (C4:C8>99): thực hiện kiểm tra từng phần tử của mảng có to hơn 99 giỏi không: kết quả TRUE, TRUE, TRUE, TRUE, FALSEDấu * trong hàm vươn lên là TRUE/ FALSE về dạng số 1/ 0Hàm SUMPRODUCT thực hiện tác dụng trả về số thành phầm thỏa mãn cả 2 điều kiện

Nguyên nhân chạm mặt lỗi value vào hàm Sumproduct

Khi bắt đầu học cách sử dụng hàm sumproduct, đa số người thường mắc phả lỗi #VALUE và dưới đó là một trong số những nguyên nhân tạo ra tình trạng này

Vùng tham chiếu bị lệch kích thước

Vùng tham chiếu trong đối số của hàm Sumproduct có size không tương tự nhau, điều này sẽ tạo nên ra lỗi #VALUE!

Trong vùng tham chiếu bao gồm ô cất lỗi #VALUE!

Trong vùng tham chiếu có một hay nhiều ô chứa lỗi #VALUE! cũng là vì sao gây ra lỗi #VALUE!

Cách khắc phục: chỉnh sửa lại tài liệu dùng để tính toán trước khi áp dụng hàm Sumproduct

Kết luận

Trên phía trên mình đã trình làng cho các bạn về hàm sumproduct. hi vọng với hầu như gì phía dẫn, phần lớn người nắm vững và rất có thể ứng dụng được vào trong quá trình của mình.