Đếm có điều kiện và kết hợp lọc dữ liệu

[thaihung85]

Dạ rất mong anh chị và các bạn hỗ trợ giúp mình đếm số tên sách (không trùng lặp) ở Cột B kèm điều kiện "ĐK" hoặc "PG" ở CỘT C và có sử dụng Filter ở CỘT A ạ.

Mình mò mãi vẫn chưa ra được kết quả.

Xin chân thành cảm ơn rất nhiều ạ.

 

[dangductuong2125]

Sử dụng công thức này D197 : Kéo xuống D198

=SUMPRODUCT(1/(COUNTIFS($A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192))*(E3=$C$6:$C$192)*(SUBTOTAL(103,OFFSET($C$6,ROW($C$6:$C$192)-6,0,1))))

B193 cũng có thể thay bằng công thức này :

=SUMPRODUCT(1/(COUNTIFS($A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192))*(SUBTOTAL(103,OFFSET($C$6,ROW($C$6:$C$192)-6,0,1))))

 

[thaihung85]

Sử dụng công thức này D197 : Kéo xuống D198

=SUMPRODUCT(1/(COUNTIFS($A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192))*(E3=$C$6:$C$192)*(SUBTOTAL(103,OFFSET($C$6,ROW($C$6:$C$192)-6,0,1))))

B193 cũng có thể thay bằng công thức này :

=SUMPRODUCT(1/(COUNTIFS($A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192))*(SUBTOTAL(103,OFFSET($C$6,ROW($C$6:$C$192)-6,0,1))))

Mình thử hình như đếm luôn các tên sách bị trùng và thiếu 1 cuốn hay sao á bạn. Xem lại giúp mình với?
Cảm ơn nhiều ạ.

 

[dangductuong2125]

File bạn có 2 dòng nhập giống hệt nhau, nên khi Remove Duplicates nó sẽ còn 186 thôi.
Đây nhé :



Công thức sửa chỗ E3 là C197 nhé :
=SUMPRODUCT(1/(COUNTIFS($A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192))*(C197=$C$6:$C$192)*(SUBTOTAL(103,OFFSET($C$6,ROW($C$6:$C$192)-6,0,1))))

 

[Nhattanktnn]

Công thức sửa chỗ E3 là C197 nhé :
=SUMPRODUCT(1/(COUNTIFS($A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192))*(C197=$C$6:$C$192)*(SUBTOTAL(103,OFFSET($C$6,ROW($C$6:$C$192)-6,0,1))))

Đề ra vẫn có chút khó hiểu, cột A để filter thì không rõ là nếu filter 2 loại khác nhau (giả sử chỉ 2 dòng), nhưng 2 dòng này lại giống nhau thì không biết sẽ tính là 1 hay 2.
Đáp án bạn đưa ra theo mình nghĩ bạn đang hiểu tính là 2, trong countifs vẫn thiếu 1 điều kiện nữa

 

[thaihung85]

File bạn có 2 dòng nhập giống hệt nhau, nên khi Remove Duplicates nó sẽ còn 186 thôi.
Đây nhé :
View attachment 291061


Công thức sửa chỗ E3 là C197 nhé :
=SUMPRODUCT(1/(COUNTIFS($A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192))*(C197=$C$6:$C$192)*(SUBTOTAL(103,OFFSET($C$6,ROW($C$6:$C$192)-6,0,1))))

Bạn xem lại giúp mình với ạ. Ở cột B - tên sách có nhiều dòng giống nhau nên ở ô B193 là đếm số tên sách (tên nào bị trùng chỉ đếm 1 lần).
Cảm ơn bạn nhiều nhé!

Bài đã được tự động gộp: 6/6/23

Đề ra vẫn có chút khó hiểu, cột A để filter thì không rõ là nếu filter 2 loại khác nhau (giả sử chỉ 2 dòng), nhưng 2 dòng này lại giống nhau thì không biết sẽ tính là 1 hay 2.
Đáp án bạn đưa ra theo mình nghĩ bạn đang hiểu tính là 2, trong countifs vẫn thiếu 1 điều kiện nữa

Mình đang cần tính là 1 ạ.

 

[Nhattanktnn]

Mình đang cần tính là 1 ạ.

Bạn thêm 1 cột phụ

 

[bebo021999]

Đây là dạng "Đếm giá trị duy nhất từ 2 cột theo 1 điều kiện"
Kết cấu như sau:
D197:

=SUM(IF($C$6:$C$192=C197,1/COUNTIFS($C$6:$C$192,C197,$A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192)))

Ctrl-Shift-Enter nếu dùng Excel phiên bản cũ từ 2016
Copy xuống
Trong file đính kèm, cột D và E dùng để test, cũng có thể dùng để filter lấy ra danh sách từng loại.

 

[dangductuong2125]

Đây là dạng "Đếm giá trị duy nhất từ 2 cột theo 1 điều kiện"
Kết cấu như sau:
D197:

=SUM(IF($C$6:$C$192=C197,1/COUNTIFS($C$6:$C$192,C197,$A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192)))

Ctrl-Shift-Enter nếu dùng Excel phiên bản cũ từ 2016
Copy xuống
Trong file đính kèm, cột D và E dùng để test, cũng có thể dùng để filter lấy ra danh sách từng loại.

Filter cột A dữ liệu có nhảy theo đâu bác.

 

[thaihung85]

Bạn thêm 1 cột phụ

Có cách nào không phải chèn thêm cột phụ không ạ? File mình đang rối thế này rồi nè bạn
Kết quả đúng rồi. Mình cảm ơn nhiều nhé!

Bài đã được tự động gộp: 6/6/23

Đây là dạng "Đếm giá trị duy nhất từ 2 cột theo 1 điều kiện"
Kết cấu như sau:
D197:

=SUM(IF($C$6:$C$192=C197,1/COUNTIFS($C$6:$C$192,C197,$A$6:$A$192,$A$6:$A$192,$B$6:$B$192,$B$6:$B$192)))

Ctrl-Shift-Enter nếu dùng Excel phiên bản cũ từ 2016
Copy xuống
Trong file đính kèm, cột D và E dùng để test, cũng có thể dùng để filter lấy ra danh sách từng loại.

Dạ đếm giá trị duy nhất cột B theo điều kiện ĐL hoặc PG ở cột C, dùng Filter cột A ạ.
Mình thử chưa ra kết quả như mong muốn.
Cảm ơn bạn nhiều nhé!

 

[bebo021999]

Filter cột A dữ liệu có nhảy theo đâu bác.

Filter cột D (hoặc E) để lấy DS mỗi loại.

 

[thaihung85]

Bạn thêm 1 cột phụ

Cột phụ này ý nghĩa là gì dạ bạn?

 

[fastfood10]

Nếu không muốn cột phụ thì thử hàm này nhé bạn.

Cell B193

=LET(TS,$B$6:$B$192,X,(SUBTOTAL(3,OFFSET($B$6,ROW(TS)-MIN(ROW(TS)),0)))*(MATCH(TS,TS,0)),Y,FREQUENCY(X,X),SUM(IF((Y=1)+(Y=2),1,0)))

Cell D197

=LET(TS,$B$6:$B$192,X,(SUBTOTAL(3,OFFSET($B$6,ROW(TS)-MIN(ROW(TS)),0)))*(MATCH(TS,TS,0)),Y,FREQUENCY(X,X),SUM(IFERROR(IF((Y=1)+(Y=2),1,0)*IF($C$6:$C$192=$C197,1,0),"")))

copy xuống D198

Bài đã được tự động gộp: 10/6/23

Cột phụ này ý nghĩa là gì dạ bạn?

Bạn có dùng subtotal rồi mà nhỉ, ý nghĩa như nhau thôi, nó chỉ tính những cột không ẩn (bởi vì cột bị ẩn đã trở thành 0)

 

[thaihung85]

Nếu không muốn cột phụ thì thử hàm này nhé bạn.

Cell B193

=LET(TS,$B$6:$B$192,X,(SUBTOTAL(3,OFFSET($B$6,ROW(TS)-MIN(ROW(TS)),0)))*(MATCH(TS,TS,0)),Y,FREQUENCY(X,X),SUM(IF((Y=1)+(Y=2),1,0)))

Cell D197

=LET(TS,$B$6:$B$192,X,(SUBTOTAL(3,OFFSET($B$6,ROW(TS)-MIN(ROW(TS)),0)))*(MATCH(TS,TS,0)),Y,FREQUENCY(X,X),SUM(IFERROR(IF((Y=1)+(Y=2),1,0)*IF($C$6:$C$192=$C197,1,0),"")))

copy xuống D198

Bài đã được tự động gộp: 10/6/23

Bạn có dùng subtotal rồi mà nhỉ, ý nghĩa như nhau thôi, nó chỉ tính những cột không ẩn (bởi vì cột bị ẩn đã trở thành 0)

Sao mình áp công thức vô bảng mà nó không hiển thị kết quả? Bạn xem lại giúp mình với ạ.
Cảm ơn bạn nhiều lắm!

 

[fastfood10]

Sao mình áp công thức vô bảng mà nó không hiển thị kết quả? Bạn xem lại giúp mình với ạ.
Cảm ơn bạn nhiều lắm!

Dùng phiên bản cũ thì nó sẽ dài dòng hơn XÍU

B193

=SUM(IF((FREQUENCY((SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)),(SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)))=1)+(FREQUENCY((SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)),(SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)))=2),1,0))

D197

=SUM(IFERROR(IF((FREQUENCY((SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)),(SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)))=1)+(FREQUENCY((SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)),(SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)))=2),1,0)*IF($C$6:$C$192=$C197,1,0),""))

Copy xuống D198

Bạn nên tạo name cho dữ liệu để đỡ phải mất công sức về sau nếu muốn sửa tham chiếu.

 

[thaihung85]

Dùng phiên bản cũ thì nó sẽ dài dòng hơn XÍU

B193

=SUM(IF((FREQUENCY((SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)),(SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)))=1)+(FREQUENCY((SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)),(SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)))=2),1,0))

D197

=SUM(IFERROR(IF((FREQUENCY((SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)),(SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)))=1)+(FREQUENCY((SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)),(SUBTOTAL(3,OFFSET($B$6,ROW($B$6:$B$192)-MIN(ROW($B$6:$B$192)),0)))*(MATCH($B$6:$B$192,$B$6:$B$192,0)))=2),1,0)*IF($C$6:$C$192=$C197,1,0),""))

Copy xuống D198

Bạn nên tạo name cho dữ liệu để đỡ phải mất công sức về sau nếu muốn sửa tham chiếu.

Mình đã áp dụng và ra đúng kết quả, nhưng với những tên sách lặp lại nhiều hơn 2 lần sẽ bị bỏ sót.
Cảm ơn bạn nhiều nhé!

 

[fastfood10]

Mình đã áp dụng và ra đúng kết quả, nhưng với những tên sách lặp lại nhiều hơn 2 lần sẽ bị bỏ sót.
Cảm ơn bạn nhiều nhé!

Mình thử rồi, có thấy bị sót đâu, bạn có thể đưa ví dụ lên không?

 

[thaihung85]

Mình thử rồi, có thấy bị sót đâu, bạn có thể đưa ví dụ lên không?

À, trong file trên có tổng số 146 tên sách, ví dụ cuốn "ĐẠO ĐỨC 4" đang lặp lại 2 lần, nếu bạn cho lập lại thêm lần nữa thì tổng số tên sách còn 145 thôi. Cho cuốn khác lặp lại trên 2 lần cũng mất tiếp.

 

[fastfood10]

À, trong file trên có tổng số 146 tên sách, ví dụ cuốn "ĐẠO ĐỨC 4" đang lặp lại 2 lần, nếu bạn cho lập lại thêm lần nữa thì tổng số tên sách còn 145 thôi. Cho cuốn khác lặp lại trên 2 lần cũng mất tiếp.

Thế không phải yêu cầu ban đầu là chỉ đếm 1 lần thôi sao ? Ví dụ có 10 sách, nếu có 1 sách lặp lại 1 lần thì tổng sách là 9, nếu lặp lại 2 lần thì tổng sách là 8, vì yêu cầu của bạn là sách lặp lại chỉ được tính 1 lần. Hay tôi vẫn chưa hiểu yêu cầu của bạn là gì ?

 

[thaihung85]

Thế không phải yêu cầu ban đầu là chỉ đếm 1 lần thôi sao ? Ví dụ có 10 sách, nếu có 1 sách lặp lại 1 lần thì tổng sách là 9, nếu lặp lại 2 lần thì tổng sách là 8, vì yêu cầu của bạn là sách lặp lại chỉ được tính 1 lần. Hay tôi vẫn chưa hiểu yêu cầu của bạn là gì ?

Yêu cầu là các tên sách trùng nhau (có lặp lại bao nhiêu lần đi nữa) thì cũng chỉ đếm 1 lần thôi bạn.