Cách sử dụng hàm SUMIF / SUMIFS - AND - OR Logic trong Excel

 
Đôi khi chúng ta cần thực hiện một số sửa đổi nhỏ đối với các công thức để làm cho chúng hiệu quả.

Cứ như vậy, SUMIF OR. Đó là một công thức nâng cao giúp bạn tăng tính linh hoạt của SUMIF. Có, bạn cũng có thể thực hiện SUMIFS. Nhưng trước khi sử dụng, hãy để tôi nói cho bạn biết một điều về SUMIF.

Trong SUMIF, bạn chỉ có thể sử dụng một tiêu chí và trong SUMIFS, bạn có thể sử dụng nhiều tiêu chí để tính tổng.

Chỉ là một thứ như thế này. Giả sử, trong SUMIFS, nếu bạn chỉ định hai tiêu chí khác nhau, thì tính năng này sẽ chỉ tính tổng những ô đáp ứng cả hai tiêu chí.

Bởi vì nó hoạt động với logic AND, nên tất cả các tiêu chí phải đáp ứng thì mới được tính một ô . Khi bạn kết hợp logic OR với SUMIF/SUMIFS, bạn có thể tính tổng các giá trị bằng cách sử dụng hai tiêu chí khác nhau cùng một lúc.

Còn chần chừ gì nữa, hãy cùng tìm hiểu công thức tuyệt vời này.

Chúng ta có thực sự cần HAY Logic không? Có ví dụ nào không?

Để tôi chỉ cho bạn một ví dụ mà chúng ta cần thêm logic OR trong SUMIFS hoặc SUMIF.

Hãy xem bảng dữ liệu bên dưới, nơi bạn có danh sách các sản phẩm với số lượng và trạng thái hiện tại của lô hàng.

bảng dữ liệu để sử dụng hoặc logic để sử dụng trong sumifs sumif

Trong dữ liệu này, bạn có hai loại sản phẩm (Bị lỗi và Hư hỏng) mà bạn cần trả lại cho nhà cung cấp của chúng tôi.

Nhưng trước đó, bạn muốn tính tổng số lượng của cả hai loại sản phẩm. Nếu sử dụng phương pháp thông thường, bạn phải áp dụng SUMIF hai lần để có tổng này.

Nhưng nếu bạn sử dụng điều kiện OR trong SUMIF thì bạn có thể lấy tổng của cả hai tích chỉ bằng một công thức .

OK, Hãy đăng ký HOẶC với SUMIFS

Trước khi bắt đầu, vui lòng tải xuống tệp mẫu này từ đây để làm theo.

Trong bất kỳ ô nào khác trong trang tính mà bạn muốn tính tổng, hãy chèn công thức bên dưới và nhấn enter.

=SUM(SUMIFS(C2:C21,B2:B21,{'Damage ','Faulty'}))

áp dụng công thức để nhận hoặc logic trong sumifs

Trong công thức trên, bạn đã sử dụng SUMIFS nhưng nếu muốn sử dụng SUMIF, bạn có thể chèn công thức bên dưới vào ô.

=SUM(SUMIF(B2:B21,{'Damage' ,'Faulty'},C2:C21))

Bằng cách sử dụng cả hai công thức trên, bạn sẽ nhận được kết quả là 540. Để xác minh chéo, chỉ cần kiểm tra tổng số theo cách thủ công.

xác minh tổng số thiệt hại và lỗi bằng sumif hoặc

< h3 class='wp-block-heading'>Cách thức hoạt động của công thức này < p>Như tôi đã nói, hàm SUMIFS sử dụng logic AND để tính tổng các giá trị. Tuy nhiên, công thức mà bạn đã sử dụng ở trên bao gồm logic OR trong đó.

 

Để hiểu công thức này, bạn phải chia nó thành ba phần khác nhau.

ba phần của công thức để sử dụng hoặc logic trong sumifs sumif
  • Điều đầu tiên là hiểu rằng bạn đã sử dụng hai tiêu chí khác nhau trong công thức này bằng cách sử dụng khái niệm mảng. Tìm hiểu thêm về mảng tại đây.
  • Vấn đề thứ hai là khi bạn chỉ định hai giá trị khác nhau bằng cách sử dụng một mảng, SUMIFS phải tìm kiếm cả hai giá trị một cách riêng biệt.
  • Điều thứ ba là ngay cả sau khi sử dụng công thức mảng, SUMIFS không thể trả về tổng của cả hai giá trị trong một ô. Vì vậy, đó là lý do tại sao bạn phải đặt nó trong hàm SUM.

Với khái niệm trên, bạn có thể tính tổng cho cả hai loại sản phẩm trong một ô.

Nó sẽ hoạt động tương tự với SUMIF và SUMIFS.

Và, bạn cũng có thể mở rộng thêm công thức của mình bằng cách chỉ định tên sản phẩm trong tiêu chí thứ hai trong SUMIFS để tính tổng cho một sản phẩm.

Sử dụng Dải tiêu chí động

Trong phần nhận xét, Shay đã hỏi tôi về việc sử dụng tham chiếu ô để thêm nhiều tiêu chí thay vì chèn chúng trực tiếp vào công thức .

Chà, đó là một câu hỏi cực kỳ hợp lệ và bạn có thể làm điều này bằng cách sử dụng một dải ô có tên động thay vì các giá trị cốt lõi.

Và để làm được điều này, bạn chỉ cần thực hiện hai sửa đổi nhỏ vào công thức của bạn.

  • Đầu tiên là thay vì sử dụng dấu ngoặc nhọn, bạn cần sử dụng một dải ô đã đặt tên (cách tốt nhất là sử dụng bảng) cho giá trị của bạn.
  • Và sau đó rằng, bạn cần nhập công thức này bằng cách sử dụng Ctrl + Shift + Enter dưới dạng công thức mảng phù hợp.

Vì vậy, bây giờ công thức của bạn sẽ là:

sử dụng dải ô đã đặt tên trong sumif hoặc

Nhiều tiêu chí với các cột khác nhau

Tôi sẽ đưa bạn đến cấp độ tiếp theo của công thức này. Chỉ cần nhìn vào bảng dữ liệu bên dưới.

sumifs sumif hoặc với dữ liệu nhiều cột

Trong bảng này, bạn có hai cột khác nhau với hai loại trạng thái khác nhau. Bây giờ, từ bảng dữ liệu này, bạn cần tính tổng số lượng sản phẩm bị hư hỏng ở trạng thái đổi trả là “Không”.

Tức là chỉ những sản phẩm Hư hỏng và Bị lỗi chưa được đổi trả. Và, công thức cho điều này sẽ là.

=SUM(SUMIFS(D2:D21,B2:B21,{'Damage','Faulty'},C2:C21,'No'))

sumifs sumif hoặc với nhiều công thức tiêu chí cột khác nhau

Kết luận

Phần hay nhất khi sử dụng kỹ thuật OR là bạn có thể thêm bao nhiêu tiêu chí vào đó. Nếu bạn chỉ muốn sử dụng logic OR thì bạn có thể sử dụng SUMIF. Và nếu bạn muốn tạo một điều kiện OR và điều kiện AND trong một công thức thì bạn có thể sử dụng SUMIFS cho điều đó.

Bạn biết đấy, đây là một trong những mẹo công thức yêu thích của tôi và tôi hy vọng bạn thấy nó hữu ích.

Bây giờ hãy cho tôi biết một điều. Bạn đã bao giờ sử dụng loại công thức này trước đây chưa? Chia sẻ quan điểm của bạn với tôi trong phần bình luận, tôi rất muốn nghe ý kiến ​​của bạn và đừng quên chia sẻ mẹo này với bạn bè của bạn.

Share

Sức khỏe