Subtotal có điều kiện

[annguyen9124]

Em có 2 cột A và B
A B
S01 1
S01 0
S02 1
S03 0

E biết hàm subtotal là tính giá trị được hiện ra và không tính giá trị ẩn.
Vậy giờ e muốn Count cột A nhưng thêm điều kiện cột B phải là 1 thì làm như thế nào ạ?
Em cảm ơn!

 

[ndu96081631]

Em có 2 cột A và B
A B
S01 1
S01 0
S02 1
S03 0

E biết hàm subtotal là tính giá trị được hiện ra và không tính giá trị ẩn.
Vậy giờ e muốn Count cột A nhưng thêm điều kiện cột B phải là 1 thì làm như thế nào ạ?
Em cảm ơn!

Làm được với điều kiện là bạn đưa file thật lên đây
(mất công làm xong, bạn ráp vào file thật sai kết quả lại la làng)

 

[annguyen9124]

Làm được với điều kiện là bạn đưa file thật lên đây
(mất công làm xong, bạn ráp vào file thật sai kết quả lại la làng)

Vâng file của e đây ạ

 

[giaiphap]

Em có 2 cột A và B
A B
S01 1
S01 0
S02 1
S03 0

E biết hàm subtotal là tính giá trị được hiện ra và không tính giá trị ẩn.
Vậy giờ e muốn Count cột A nhưng thêm điều kiện cột B phải là 1 thì làm như thế nào ạ?
Em cảm ơn!

Tôi nghĩ cái CountIfs là ra rồi sao lại Subtotal vậy?

 

[ndu96081631]

Tôi nghĩ cái CountIfs là ra rồi sao lại Subtotal vậy?

COUNTIFS rồi làm sao có tác dụng khi ẩn dòng hả bạn?

 

[ndu96081631]

Vâng file của e đây ạ

Chẳng hạn là vầy:

=SUMPRODUCT(SUBTOTAL(103,OFFSET($A$1,ROW(INDIRECT("1:"&ROWS($A$2:$A$12))),))*$B$2:$B$12)

Đếm theo điều kiện: Những dòng đang hiện + cột B<>0)

 

[annguyen9124]

Chẳng hạn là vầy:

=SUMPRODUCT(SUBTOTAL(103,OFFSET($A$1,ROW(INDIRECT("1:"&ROWS($A$2:$A$12))),))*$B$2:$B$12)

Đếm theo điều kiện: Những dòng đang hiện + cột B<>0)

Dạ em cám ơn ạ. Em thử ok rồi ạ.
Nhưng có thể giải thích cho e thêm 1 chút k ạ?
E k thấy đoạn hàm nào là để điều kiện cột B=1 ạ
Em cám ơn thầy!

 

[annguyen9124]

Thầy ơi E vừa sửa thử số 1 thành 2 thì nó nhảy thêm 1 thầy ạ. E đang cần đếm cái mã sp mà. Nếu điều kiện của e mà >0 thì cột B có bao nhiêu số nó đếm hết thầy ạ
hình như nó tính tổng của cột B chứ k phải đếm ạ

 

[ndu96081631]

Thầy ơi E vừa sửa thử số 1 thành 2 thì nó nhảy thêm 1 thầy ạ. E đang cần đếm cái mã sp mà. Nếu điều kiện của e mà >0 thì cột B có bao nhiêu số nó đếm hết thầy ạ
hình như nó tính tổng của cột B chứ k phải đếm ạ

Muốn điều kiện B=1 thì sửa *$B$2:$B$12 thành *($B$2:$B$12=1)
Muốn điều kiện B<>0 thì sửa *$B$2:$B$12 thành *($B$2:$B$12<>0)
Muốn điều kiện B>0 thì sửa *$B$2:$B$12 thành *($B$2:$B$12>0)
vân vân... và... mây mây...

 

[annguyen9124]

Muốn điều kiện B=1 thì sửa *$B$2:$B$12 thành *($B$2:$B$12=1)
Muốn điều kiện B<>0 thì sửa *$B$2:$B$12 thành *($B$2:$B$12<>0)
Muốn điều kiện B>0 thì sửa *$B$2:$B$12 thành *($B$2:$B$12>0)
vân vân... và... mây mây...

Em cám ơn thầy nhiều ạ

 

[echo07]

Xin chào cả nhà. Tôi có vấn đề cần được giúp đỡ với hàm tính tổng sau khi Fillter. Cụ thể là sẽ tính tổng các giá trị ở cột "B" sau khi Fillter ở cột "C" theo các điều kiện ở cột "A". Chi tiết cụ thể như file đính kèm.

Xin cảm ơn cả nhà trước, tôi đi lo chống bão số 9 (Usagi) cái đã.

 

[Phan Thế Hiệp]

Xin chào cả nhà. Tôi có vấn đề cần được giúp đỡ với hàm tính tổng sau khi Fillter. Cụ thể là sẽ tính tổng các giá trị ở cột "B" sau khi Fillter ở cột "C" theo các điều kiện ở cột "A". Chi tiết cụ thể như file đính kèm.

Xin cảm ơn cả nhà trước, tôi đi lo chống bão số 9 (Usagi) cái đã.

Thử:

E2=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$2,ROW($1:$100),))*($A$3:$A$102=E$1)*$B$3:$B$102)

Enter, fill qua phải 2 cột

Thân

 

[echo07]

Thử:

E2=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$2,ROW($1:$100),))*($A$3:$A$102=E$1)*$B$3:$B$102)

Enter, fill qua phải 2 cột

Thân

Xin cảm ơn và chúc bạn tuần mới vui vẻ, hạnh phúc.

 

[nhatanh2197]

Thử:

E2=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$2,ROW($1:$100),))*($A$3:$A$102=E$1)*$B$3:$B$102)

Enter, fill qua phải 2 cột

Thân

Mình không hiểu công thức này lắm nên đã tách từng công thức nhỏ ra xem thì thấy công thức SUBTOTAL(3, OFFSET($A$2,ROW($1:$100),)) cho kết quả bằng 1 nhưng khi COUNT trong subtotal thì kết quả phải lớn hơn 1 chứ nhỉ? Bạn giải thích giúp mình với.
Cảm ơn bạn nhiều

 

[Phan Thế Hiệp]

Mình không hiểu công thức này lắm nên đã tách từng công thức nhỏ ra xem thì thấy công thức SUBTOTAL(3, OFFSET($A$2,ROW($1:$100),)) cho kết quả bằng 1 nhưng khi COUNT trong subtotal thì kết quả phải lớn hơn 1 chứ nhỉ? Bạn giải thích giúp mình với.
Cảm ơn bạn nhiều

Xem giải thích trong file đính kèm.

Thân

 

[nhatanh2197]

Xem giải thích trong file đính kèm.

Thân

Em vẫn chưa hiểu lắm ạ. Công thức SUBTOTAL(3, OFFSET($A$2,ROW($1:$100),)) tức là đếm số ô không trống trong mảng A3:A100, vậy kết quả là 18 hoặc 1 số lớn hơn 1 nhưng tại sao nó chỉ ra kết quả 0, 1 vậy ạ.
Em cảm ơn

 

[Phan Thế Hiệp]

Em vẫn chưa hiểu lắm ạ. Công thức SUBTOTAL(3, OFFSET($A$2,ROW($1:$100),)) tức là đếm số ô không trống trong mảng A3:A100, vậy kết quả là 18 hoặc 1 số lớn hơn 1 nhưng tại sao nó chỉ ra kết quả 0, 1 vậy ạ.
Em cảm ơn

Bạn cứ hình dung cách hoạt động của OFFSET($A$2,ROW($1:$100),) như sau: Giả sử lọc 60%

1. Dòng đầu tiên: SUBTOTAL(3, OFFSET($A$2,ROW($1:$1),) ) = SUBTOTAL(3, Data!A3 ) = 0 (do dòng 3 bị ẩn, vì không thỏa = 60%)
2. Dòng kế tiếp: SUBTOTAL(3, OFFSET($A$2,ROW($2:$2),) ) = SUBTOTAL(3, Data!A4 ) = 1 (do dòng 4 đang hiện, vì thỏa = 60%)
3. Dòng kế tiếp: SUBTOTAL(3, OFFSET($A$2,ROW($3:$3),) ) = SUBTOTAL(3, Data!A5 ) = 0 (do dòng 5 bị ẩn, vì không thỏa = 60%)
4. Dòng kế tiếp: SUBTOTAL(3, OFFSET($A$2,ROW($4:$4),) ) = SUBTOTAL(3, Data!A6 ) = 1 (do dòng 6 đang hiện, vì thỏa = 60%)
5. .........Cứ tiếp tục như vậy. (Xem công thức cột B "Tương đương", sheet "Giaithich")

Bạn lưu ý rằng hàm mảng ROW($1:$100) đang nằm ở đối số "Hàng" (Rows), không phải nằm ở đối số "Chiều Dài" (Height) của hàm OFFSET().
(Xem thêm cú pháp của Offset()= OFFSET(reference, rows, cols, [height], [width]) - trích theo: https://support.office.com/vi-vn/article/offset-hàm-offset-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)
Cho nên Offset() nhảy đến lấy giá trị từng ô một, và Subtotal( 3, "dòng đơn") chỉ đếm trên từng ô đơn đó, chứ không đếm toàn bộ cột A.

Thân

 

[nhatanh2197]

Bạn cứ hình dung cách hoạt động của OFFSET($A$2,ROW($1:$100),) như sau: Giả sử lọc 60%

1. Dòng đầu tiên: SUBTOTAL(3, OFFSET($A$2,ROW($1:$1),) ) = SUBTOTAL(3, Data!A3 ) = 0 (do dòng 3 bị ẩn, vì không thỏa = 60%)
2. Dòng kế tiếp: SUBTOTAL(3, OFFSET($A$2,ROW($2:$2),) ) = SUBTOTAL(3, Data!A4 ) = 1 (do dòng 4 đang hiện, vì thỏa = 60%)
3. Dòng kế tiếp: SUBTOTAL(3, OFFSET($A$2,ROW($3:$3),) ) = SUBTOTAL(3, Data!A5 ) = 0 (do dòng 5 bị ẩn, vì không thỏa = 60%)
4. Dòng kế tiếp: SUBTOTAL(3, OFFSET($A$2,ROW($4:$4),) ) = SUBTOTAL(3, Data!A6 ) = 1 (do dòng 6 đang hiện, vì thỏa = 60%)
5. .........Cứ tiếp tục như vậy. (Xem công thức cột B "Tương đương", sheet "Giaithich")

Bạn lưu ý rằng hàm mảng ROW($1:$100) đang nằm ở đối số "Hàng" (Rows), không phải nằm ở đối số "Chiều Dài" (Height) của hàm OFFSET().
(Xem thêm cú pháp của Offset()= OFFSET(reference, rows, cols, [height], [width]) - trích theo: https://support.office.com/vi-vn/article/offset-hàm-offset-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)
Cho nên Offset() nhảy đến lấy giá trị từng ô một, và Subtotal( 3, "dòng đơn") chỉ đếm trên từng ô đơn đó, chứ không đếm toàn bộ cột A.

Thân

Vâng em hiểu rồi ạ. Cảm ơn anh nhiều

 

[Dothuy2110]

Thầy cho em hỏi ví dụ em muốn tính subtotal của cột B với điều kiện cột A là Tổng ạ
A B
Tổng 15
Hiệu 20
Tổng 5
Tích 10
Hiệu 6

 

[leonguyenz]

Thầy cho em hỏi ví dụ em muốn tính subtotal của cột B với điều kiện cột A là Tổng ạ
A B
Tổng 15
Hiệu 20
Tổng 5
Tích 10
Hiệu 6

Công thức:

=SUMPRODUCT(SUBTOTAL(103,OFFSET($A$1,ROW($1:$5)-1,))*($A$1:$A$5="Tổng")*$B$1:$B$5)