Hướng dẫn tính tổng những ô xen kẽ trong Excel bằng hàm SUMIF cùng SUMPRODUCT
Tùy trực thuộc vào độ dài cùng độ tinh vi của bảng dữ liệu, bạn hãy xem thêm hướng dẫn cả 2 hàm SUMIF với SUMPRODUCT để chắt lọc được cách tính tổng các ô tức thời nhau vào Excel thuận tiện, tương xứng nhất cùng với mình.
Bạn đang xem: Cộng các ô cách nhau trong excel
Giả sử các bạn có một bảng lợi nhuận tổng phù hợp của nhân viên cấp dưới từ 2 khu không giống nhau với mã ID không giống nhau là KD1 với KD2. Để tính tổng lệch giá cho nhân viên cấp dưới từng quần thể vực, bạn cũng có thể thực hiện một trong 2 phương pháp tính tổng các ô ko liền nhau trong Excel bởi hàm SUMIF dưới đây:
Cách này yên cầu bạn buộc phải tạo thêm 2 cột phụ tương xứng với 2 mã nhân viên. Sau đó mới triển khai tính tổng doanh thu theo từng mã. Tuy nhiên, bí quyết hàm tính tổng các ô xen kẽ trong Excel sẽ đối kháng giản, dễ hiểu hơn.
Cách triển khai như sau:
Bước 1: Bạn tạo thêm những cột phụ yêu cầu tính tổng cùng nhập hàm tại ô C2 như sau:
=IFERROR(SEARCH($C$1,A2),0)
Sau đó ấn Enter và xào luộc công thức cho các ô còn lại trong cột.
Ý nghĩa:
Hàm SEARCH: Giúp tìm quý hiếm KD1 sinh hoạt ô C1 có lộ diện trong ô A2 tuyệt không. Nếu tất cả thì hàm vẫn trả về vị trí ký kết tự bước đầu xuất hiện trong ô A2, nếu không thì báo lỗi #VALUE.Hàm IFERROR: Trường hòa hợp hàm tìm kiếm ở bên trên trả về lỗi #ERROR, thì khi kết phù hợp với hàm IFERROR, kết quả sẽ trả về bởi 0.
Nhập hàm như hình
Bước 2: Nhập hàm trên ô D2 và xào nấu hàm xuống những ô sót lại trong cột D: =IFERROR(SEARCH($D$1,A2),0)
Nhập hàm như hình cùng ấn Enter
Nhập hàm như hình cùng ấn Enter.Sau khi vẫn tính kết thúc cột phụ, bạn thực hiện hàm SUMIF để tính tổng lợi nhuận không ngay tắp lự nhau như sau:
Nhập hàm: =SUMIF(C2:C10,">0",B2:B10) để tính tổng doanh thu của nhân viên có mã KD1Nhập hàm: =SUMIF(D2:D10,">0",B2:B10) nhằm tính tổng lệch giá của nhân viên cấp dưới có mã KD2
Cách tính tổng những ô ko liền nhau vào Excel bằng hàm SUMIF 1-1 giản
Cách 2: Nhập hàm SUMIF trực tiếp
Nếu bạn có nhu cầu tính tổng các ô xen kẽ trong Excel nhanh lẹ hơn, hãy áp dụng trực tiếp hàm SUMIF như sau:
Vùng điều kiện = A2:A10 (ID toàn bộ nhân viên).Điều kiện = “”&F2&”” nghĩa là thêm dấu ở trước và sau ô F2 (ô giá bán trị buộc phải lấy).Vùng bắt buộc tính tổng = B2:B10Khi đó, bí quyết hàm bạn phải nhập: =SUMIF(A2:A10,""&J2&"",B2:B10) cùng ấn Enter để dấn kết quả.
Nhập hàm tính tổng các ô xen kẽ nhau trong Excel như hình
Tương tự, các bạn thay F2 bằng F3 nhằm tính tổng lợi nhuận cho nhân viên có mã KD2 như sau: =SUMIF(A2:A10,""&F3&"",B2:B10).
2. Tính tổng các ô xen kẽ trong Excel bởi SUMPRODUCT
Bên cạnh hàm SUMIF, chúng ta cũng có thể sử dụng hàm SUMPRODUCT nhằm tính tổng trong Excel. Cách tính tổng các ô không liền nhau vào Excel bằng hàm SUMPRODUCT chỉ việc 1 cách thực hiện, nhưng phương pháp sẽ tinh vi hơn so với hàm SUMIF.
Nhập hàm trên ô G2 để tính tổng lợi nhuận của những nhân viên bao gồm mã KD1:
=SUMPRODUCT(($B$2:$B$10)(IFERROR(SEARCH(F2,$A$2:$A$10),0)>0))
Trong đó:
Các hàm IFERROR và tìm kiếm có ý nghĩa tương từ như trên.Hàm SUMPRODUCT giúp bạn so sánh tác dụng trả về của phép tính trên với 0 và tiến hành tính tổng khi hiệu quả trả về những giá trị to hơn 0.Dấu có công dụng nối quý giá trước và sau nó
Cách tính tổng các ô ko liền nhau vào Excel bằng hàm SUMPRODUCT
Trên đó là các cách tính tổng các ô không liền nhau vào Excel với 2 hàm thịnh hành là SUMIF cùng SUMPRODUCT. Chúc bạn thực hiện thành công!
3 phương pháp tính tổng các ô cùng màu trong Excel, Google Sheet cấp tốc chóng
4 hàm tính tổng vào Excel có đk | tốt nhất định cần biết
4+ phương pháp ẩn số 0 trong Excel ở đầy đủ vị trí cực đơn giản
Excel đến cdvhnghean.edu.vn 365 Excel đến cdvhnghean.edu.vn 365 d&#x
E0;nh cho m&#x
E1;y Mac Excel mang lại web Excel 2021 Excel 2021 cho Mac Excel 2019 Excel 2019 mang đến Mac Excel 2016 Excel năm 2016 cho Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 xem th&#x
EA;m...Ẩn bớt
Bạn áp dụng hàm SUMIF nhằm tính tổng những giá trị trong một phạm vi đáp ứng tiêu chí mà các bạn xác định. Ví dụ: giả sử vào một cột chứa những số, bạn chỉ hy vọng tính tổng phần nhiều giá trị to hơn 5. Chúng ta cũng có thể sử dụng cách làm sau: =SUMIF(B2:B25,">5")
Mẹo:
Nếu muốn, bạn cũng có thể áp dụng tiêu chuẩn cho một phạm vi, rồi tính tổng các giá trị tương xứng trong một phạm vi khác. Ví dụ: phương pháp =SUMIF(B2:B5, "John", C2:C5) chỉ tính tổng những giá trị trong phạm vi C2:C5, trong những số đó các ô khớp ứng ở phạm vi B2:B5 bằng "John".
Quan trọng: Hàm SUMIF trả về kết quả không chính xác khi các bạn dùng nó để khớp những chuỗi dài hơn nữa 255 cam kết tự hoặc với chuỗi #VALUE!.
Cú pháp
SUMIF(range, criteria,
Cú pháp hàm SUMIF tất cả các đối số sau đây:
phạm vi Bắt buộc. Phạm vi ô bạn muốn được đánh giá theo tiêu chí. Các ô vào mỗi phạm vi phải là số hoặc tên, mảng giỏi tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở format Excel tiêu chuẩn (ví dụ mặt dưới).
criteria Bắt buộc. Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các ô nào. Ký tự đại diện rất có thể được bao gồm - vệt chấm hỏi (?) để khớp với bất kỳ ký tự đơn nào, vết sao (*) nhằm khớp với bất kỳ chuỗi ký kết tự nào. Nếu bạn có nhu cầu tìm một lốt chấm hỏi hay lốt sao thực sự, hãy gõ dấu ngã (~) trước cam kết tự.
Ví dụ: tiêu chí rất có thể được biểu lộ là 32, ">32", B5, "3?", "apple*", "*~?", hoặc TODAY().
Quan trọng: Mọi tiêu chí văn bản hoặc mọi tiêu chí bao gồm biểu tượng lô-gic hoặc toán học đều phải được đặt trong dấu ngoặc kép ("). Nếu tiêu chí ở dạng số, ko cần dấu ngoặc kép.
sum_range Tùy chọn. Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là các ô đã xác định vào đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range (chính các ô đã được áp dụng tiêu chí).
Sum_range buộc phải bao gồm cùng size và bề ngoài với dải ô. Ví như không, hiệu suất có thể bị tác động và công thức sẽ tính tổng một phạm vi ô bắt đầu với ô đầu tiên trong sum_range nhưng gồm cùng size với phạm vi. Ví dụ:
| A1:A5 | B1:B5 | B1:B5 |
| A1:A5 | B1:K5 | B1:B5 |
Ví dụ
Ví dụ 1
Sao chép tài liệu ví dụ trong bảng tiếp sau đây và dán lại ô A1 của một bảng tính Excel mới. Để các công thức hiển thị kết quả, nên chọn chúng, dìm F2 và sau đó nhấn Enter. Nếu cần, chúng ta có thể điều chỉnh độ rộng lớn cột nhằm xem tất cả dữ liệu.
| $100.000 | $7,000 | $250,000 |
| $200,000 | $14,000 | |
| $300,000 | $21,000 | |
| $400,000 | $28,000 | |
| Công thức | Mô tả | Kết quả |
| =SUMIF(A2:A5,">160000",B2:B5) | Tổng tiền hoa hồng cho các giá trị tài sản lớn hơn $160.000. | $63,000 |
| =SUMIF(A2:A5,">160000") | Tổng các giá trị tài sản lớn hơn $160.000. | $900,000 |
| =SUMIF(A2:A5,300000,B2:B5) | Tổng tiền hoa hồng cho các giá trị tài sản bằng $300.000. | $21,000 |
| =SUMIF(A2:A5,">" & C2,B2:B5) | Tổng tiền hoa hồng cho những giá trị tài sản to hơn giá trị trên C2. | $49,000 |
Ví dụ 2
Sao chép dữ liệu ví dụ vào bảng dưới đây và ốp lại ô A1 của một bảng tính Excel mới. Để những công thức hiển thị kết quả, hãy lựa chọn chúng, dấn F2 và tiếp nối nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng lớn cột nhằm xem toàn bộ dữ liệu.
| Rau | Cà chua | $2,300 |
| Rau | Cần tây | $5,500 |
| Trái cây | Cam | $800 |
| Bơ | $400 | |
| Rau | Cà rốt | $4,200 |
| Trái cây | Táo | $1,200 |
| Công thức | Mô tả | Kết quả |
| =SUMIF(A2:A7,"Trái cây",C2:C7) | Tổng doanh thu của tổng thể thực phẩm trong đội "Trái cây". | $2,000 |
| =SUMIF(A2:A7,"Rau",C2:C7) | Tổng lệch giá của toàn cục thực phẩm trong nhóm "Rau". | $12,000 |
| =SUMIF(B2:B7,"*es",C2:C7) | Tổng doanh thu của toàn cục thực phẩm có tên hoàn thành là "es" (Cà chua, Cam và Táo). |