Tính tổng có điều kiện (1 người xem)

[JinRua]

Xin chào các anh chị trong diễn đàn !
Em có một vấn đề cần thống kê như sau: Tại cột "A" có các kí hiệu A, B, C, D, E, ... (Mỗi dòng hoặc một số dòng có cùng một kí hiệu)
Tại cột "B" tương tự như cột "A" : AB, BC,DE, FE, ... (Các dòng chứa kí hiệu bất kì không theo quy luật)
Em cần đếm tất cả các dòng (hoặc các ô ở cột "C" ) thỏa mãn điều kiện ở cột "A" có hoặc A, B, C; và cột "B" có BC, DE, FE.
Em dùng 2003. Nhờ các anh chị chỉ giúp phải dùng công thức gì.

 

[nhat chi mai]

Xin chào các anh chị trong diễn đàn !
Em có một vấn đề cần thống kê như sau: Tại cột "A" có các kí hiệu A, B, C, D, E, ... (Mỗi dòng hoặc một số dòng có cùng một kí hiệu)
Tại cột "B" tương tự như cột "A" : AB, BC,DE, FE, ... (Các dòng chứa kí hiệu bất kì không theo quy luật)
Em cần đếm tất cả các dòng (hoặc các ô ở cột "C" ) thỏa mãn điều kiện ở cột "A" có hoặc A, B, C; và cột "B" có BC, DE, FE.
Em dùng 2003. Nhờ các anh chị chỉ giúp phải dùng công thức gì.

Bạn hỏi tính tổng mà trong điều kiện bài bạn bảo đếm...hihi, bạn dùng hàm countif

 

[leonguyenz]

COUNTIF, COUNTIFS, SUMPRODUCT, ... không có file thì khó hiểu được ý định của bạn.

 

[JinRua]

Vấn đề là ở chỗ em không biết đếm nhiều điều kiện: Cột "A" một số điều kiện + cột "B" cũng một số điều kiện nữa.
Ví dụ: cột A ={ "A"; "B";"C"}, cột B= {"AB";"CD";"EF"} thì đếm tất.
Cảm ơn các anh chị giúp đỡ !

 

[vanle33]

Vấn đề là ở chỗ em không biết đếm nhiều điều kiện: Cột "A" một số điều kiện + cột "B" cũng một số điều kiện nữa.
Ví dụ: cột A ={ "A"; "B";"C"}, cột B= {"AB";"CD";"EF"} thì đếm tất.
Cảm ơn các anh chị giúp đỡ !

Chắc dùng Countifs() để đếm với nhiều điều kiện (nếu bạn dùng Excel 2007 hay 2010)

 

[JinRua]

Em dùng 2003. Em gửi file nhờ mọi người giúp.

 

[leonguyenz]

=SUM(1/COUNTIF(Vùng,Vùng)) kết thúc bằng Ctrl+Shift+Enter

 

[JinRua]

Bạn leonguyenz ơi mình không hiểu công thức của bạn. Mình chỉ thấy dữ liệu là 2 vùng vậy điều kiện các kí tự ở đâu, thay đổi như thế nào.
Kết quả theo mình đếm thì là 10 thôi.

 

[nhat chi mai]

Bạn leonguyenz ơi mình không hiểu công thức của bạn. Mình chỉ thấy dữ liệu là 2 vùng vậy điều kiện các kí tự ở đâu, thay đổi như thế nào.
Kết quả theo mình đếm thì là 10 thôi.

Anh ấy sử dụng công thức mảng nên bạn cứ làm xong rồi thao tác cuối cùng là Ctrl+Shift+Enter là được

 

[Hong.Van]

Bạn leonguyenz ơi mình không hiểu công thức của bạn. Mình chỉ thấy dữ liệu là 2 vùng vậy điều kiện các kí tự ở đâu, thay đổi như thế nào.
Kết quả theo mình đếm thì là 10 thôi.

Cthức của bạn đây

=SUMPRODUCT((($A$2:$A$37="A1")+($A$2:$A$37="A2")+($A$2:$A$37="A3")+($A$2:$A$37="A4"))*(($B$2:$B$37="AB")+($B$2:$B$37="CD")))

P/s: Bạn lưu ý khi gởi File, ngoài mô tả, nên có KQ làm = tay
Vợ chồng "với nhau" nhiều lúc không hiểu ý nhau còn bị "trật duộc" nói chi là ...

 

[Ba Tê]

Cthức của bạn đây

=SUMPRODUCT((($A$2:$A$37="A1")+($A$2:$A$37="A2")+($A$2:$A$37="A3")+($A$2:$A$37="A4"))*(($B$2:$B$37="AB")+($B$2:$B$37="CD")))

P/s: Bạn lưu ý khi gởi File, ngoài mô tả, nên có KQ làm = tay
Vợ chồng "với nhau" nhiều lúc không hiểu ý nhau còn bị "trật duộc" nói chi là ...

Vợ chồng không hiểu ý nhau nếu có "trật giuộc" thì làm lại, hổng phải vợ chồng thì "trớt quớt" luôn.
Cái "dzụ" này 10 bài mới, có hêt 7 bài "trật giuộc".
Híc!

 

[ndu96081631]

Bài này mà dùng COUNTIFS thì.. ngon tuyệt

=SUMPRODUCT(COUNTIFS($A$2:$A$37,{"A1","A2","A3","A4"},$B$2:$B$37,{"AB";"CD"}))

 

[Hong.Van]

Bài này mà dùng COUNTIFS thì.. ngon tuyệt

=SUMPRODUCT(COUNTIFS($A$2:$A$37,{"A1","A2","A3","A4"},$B$2:$B$37,{"AB";"CD"}))

Lấy cthức ở trên về, sửa dấu , và ;

=SUMPRODUCT(COUNTIFS($A$2:$A$37;{"A1";"A2";"A3";"A4"};$B$2:$B$37;{"AB";"CD"}))

Kết quả ra bằng 3, kết qủa đúng là bằng 10
Hồng biết cthức trên của em sai chỗ nào?
Em cảm ơn

 

[ndu96081631]

Lấy cthức ở trên về, sửa dấu , và ;

=SUMPRODUCT(COUNTIFS($A$2:$A$37;{"A1";"A2";"A3";"A4"};$B$2:$B$37;{"AB";"CD"}))

Kết quả ra bằng 3, kết qủa đúng là bằng 10
Hồng biết cthức trên của em sai chỗ nào?
Em cảm ơn

Bậy không!
Dấu phẩy và chấm phẩy trong công thức của tôi là có hàm ý
Mảng {"A1","A2","A3","A4"} là mảng ngang
Mảng {"AB";"CD"} là mảng dọc
Vậy nếu bạn không biết mảng dọc trên máy tính của bạn dùng dấu phân cách gì thì có thể phối hợp với TRANSPOSE (khỏi suy nghĩ)

=SUMPRODUCT(COUNTIFS($A$2:$A$37;{"A1";"A2";"A3";"A4"};$B$2:$B$37;TRANSPOSE({"AB";"CD"})))

Test xem
(làm biếng thay đổi control panel để test quá)

 

[Hong.Van]

Bậy không!
Dấu phẩy và chấm phẩy trong công thức của tôi là có hàm ý
Mảng {"A1","A2","A3","A4"} là mảng ngang
Mảng {"AB";"CD"} là mảng dọc
Vậy nếu bạn không biết mảng dọc trên máy tính của bạn dùng dấu phân cách gì thì có thể phối hợp với TRANSPOSE (khỏi suy nghĩ)

=SUMPRODUCT(COUNTIFS($A$2:$A$37;{"A1";"A2";"A3";"A4"};$B$2:$B$37;TRANSPOSE({"AB";"CD"})))

Test xem
(làm biếng thay đổi control panel để test quá)

Cthức này thì ngon rồi ạ
nhưng cthức trên lúc ban đầu em sửa như thế này thì vẫn bị báo lỗi

=SUMPRODUCT(COUNTIFS($A$2:$A$37;{"A1";"A2";"A3";"A4"};$B$2:$B$37;{"AB","CD"}))

 

[ndu96081631]

Cthức này thì ngon rồi ạ
nhưng cthức trên lúc ban đầu em sửa như thế này thì vẫn bị báo lỗi

=SUMPRODUCT(COUNTIFS($A$2:$A$37;{"A1";"A2";"A3";"A4"};$B$2:$B$37;{"AB","CD"}))

Để biết dấu phân cách của mảng dọc trên máy bạn là gì, bạn có thể thí nghiệm:
- Mở file Excel trắng
- Gõ số 1 vào cell A1, số 2 vào cell A2 và số 3 vào cell A3
- Tại B1, gõ công thức =A1:A3 rồi Enter
- Quét chọn chuổi công thức =A1:A3 trên thanh Formula rồi bấm F9 ---> Bạn sẽ biết dấu phân cách của mảng dọc trên máy bạn là dấu gì liền

 

[Hong.Van]

Để biết dấu phân cách của mảng dọc trên máy bạn là gì, bạn có thể thí nghiệm:
- Mở file Excel trắng
- Gõ số 1 vào cell A1, số 2 vào cell A2 và số 3 vào cell A3
- Tại B1, gõ công thức =A1:A3 rồi Enter
- Quét chọn chuổi công thức =A1:A3 trên thanh Formula rồi bấm F9 ---> Bạn sẽ biết dấu phân cách của mảng dọc trên máy bạn là dấu gì liền

Của em dấu chấm fẩy.
Như vậy em thay dấu fẩy thành chấm fẩy như bài 13 là đúng rồi, nhưng sao KQ =3 ????
Hổng hiểu luôn!
Cảm ơn Thầy!

 

[ndu96081631]

Của em dấu chấm fẩy.
Như vậy em thay dấu fẩy thành chấm fẩy như bài 13 là đúng rồi, nhưng sao KQ =3 ????
Hổng hiểu luôn!
Cảm ơn Thầy!

Vậy thì tìm hiểu dấu phân cách của mảng ngang (vì 1 trong 2 dấu phân cách này sẽ "khác người")
- Gõ số 1 vào A1, số 2 vào B1 và số 3 vào C1
- Gõ vào A2 công thức =A1:C1
- Quét chuổi công thức =A1:C1 trên thanh Formula rồi bấm F9

 

[Hong.Van]

Vậy thì tìm hiểu dấu phân cách của mảng ngang (vì 1 trong 2 dấu phân cách này sẽ "khác người")
- Gõ số 1 vào A1, số 2 vào B1 và số 3 vào C1
- Gõ vào A2 công thức =A1:C1
- Quét chuổi công thức =A1:C1 trên thanh Formula rồi bấm F9

Kết qủa là ={1\2\3}
Và cthức bây giờ sửa lại

=SUMPRODUCT(COUNTIFS($A$2:$A$37;{"A1"\"A2"\"A3"\"A4"};$B$2:$B$37;{"AB";"CD"}))

Hèn chi, trước đây đụng 1 bài như thế thì bị tổ trác!
Em cảm ơn!

 

[Hong.Van]

Lấy cthức ở trên về, sửa dấu , và ;

=SUMPRODUCT(COUNTIFS($A$2:$A$37;{"A1";"A2";"A3";"A4"};$B$2:$B$37;{"AB";"CD"}))

Kết quả ra bằng 3, kết qủa đúng là bằng 10
Hồng biết cthức trên của em sai chỗ nào?
Em cảm ơn

Các Thầy cho em hỏi, Cthức trên có kết qủa =3, vậy nó tính cái gì? Em dò rồi mà chưa ra?
Em cảm ơn!

 

[ndu96081631]

Các Thầy cho em hỏi, Cthức trên có kết qủa =3, vậy nó tính cái gì? Em dò rồi mà chưa ra?
Em cảm ơn!

Vì công thức của bạn sẽ tương đương với cái này:

=SUMPRODUCT(($A$2:$A$37&$B$2:$B$37="A1AB")+($A$2:$A$37&$B$2:$B$37="A2CD"))

2 điều kiện A3 và A4 không được xét đến
Trong khi nếu {"A1";"A2";"A3";"A4"} là mảng dọc và {"AB"\"CD"} là mảng ngang thì nó sẽ tổng hợp như 1 bảng tra 2 chiều thế này

 

[ptm0412]

Vì công thức của bạn sẽ tương đương với cái này:

=SUMPRODUCT(($A$2:$A$37&$B$2:$B$37="A1AB")+($A$2:$A$37&$B$2:$B$37="A2CD"))

2 điều kiện A3 và A4 không được xét đến
Trong khi nếu {"A1";"A2";"A3";"A4"} là mảng dọc và {"AB"\"CD"} là mảng ngang thì nó sẽ tổng hợp như 1 bảng tra 2 chiều thế này

Như vậy dùng SUM cũng được, không cần sumproduct:

=SUM(COUNTIFS($A$2:$A$37,{"A1","A2","A3","A4"},$B$2:$B$37,{"AB";"CD"}))

 

[JinRua]

Em cảm ơn các anh chị rất nhiều nhưng cho em hỏi: Em dùng Excel 2003 các công thức em lấy về đều báo lỗi #NAME? chắc là vì Excel 2003 không hỗ trợ công thức "COUNTIFS()". Có công thức nào khác dùng cho Excel 2003 không ạ.

 

[JinRua]

Em đã thử công thức :
=SUMPRODUCT(($A$2:$A$37&$B$2:$B$37="A1AB")+($A$2:$A$37&$B$2:$B$37="A2AB")+($A$2:$A$37&$B$2:$B$37="A3AB")+($A$2:$A$37&$B$2:$B$37="A4AB")+($A$2:$A$37&$B$2:$B$37="A1CD")+($A$2:$A$37&$B$2:$B$37="A2CD")+($A$2:$A$37&$B$2:$B$37="A3CD")+($A$2:$A$37&$B$2:$B$37="A4CD"))
Thì ra kết quả nhưng sao nó dài thế, có rút gọn được không ạ ?

 

[Hong.Van]

Em đã thử công thức :
=SUMPRODUCT(($A$2:$A$37&$B$2:$B$37="A1AB")+($A$2:$A$37&$B$2:$B$37="A2AB")+($A$2:$A$37&$B$2:$B$37="A3AB")+($A$2:$A$37&$B$2:$B$37="A4AB")+($A$2:$A$37&$B$2:$B$37="A1CD")+($A$2:$A$37&$B$2:$B$37="A2CD")+($A$2:$A$37&$B$2:$B$37="A3CD")+($A$2:$A$37&$B$2:$B$37="A4CD"))
Thì ra kết quả nhưng sao nó dài thế, có rút gọn được không ạ ?

Thì Cthức ở Bài #10 là gọn nhất rồi đó

 

[JinRua]

Thì Cthức ở Bài #10 là gọn nhất rồi đó

Vâng em thấy rồi, em cảm ơn. Vì dữ liệu của em không phải 4 mà 20 dữ liệu ở cột A cơ và 8 dữ liệu ở cột B nên em muốn gọn hơn.

 

[Ba Tê]

Vâng em thấy rồi, em cảm ơn. Vì dữ liệu của em không phải 4 mà 20 dữ liệu ở cột A cơ và 8 dữ liệu ở cột B nên em muốn gọn hơn.

Nhiều quá thì dùng Hàm tự tạo đi

Public Function GPE(Rg1, Rg2, Rg3) As Long
Dim Rng(), Cll As Range, Dic1 As Object, Dic2 As Object, I As Long, Tem As Long
Set Dic1 = CreateObject("Scripting.Dictionary")
Set Dic2 = CreateObject("Scripting.Dictionary")
Rng = Rg1.Value
For Each Cll In Rg2
    Dic1.Add (Cll), ""
Next
For Each Cll In Rg3
    Dic2.Add (Cll), ""
Next
For I = 1 To UBound(Rng, 1)
    If Dic1.exists(Rng(I, 1)) And Dic2.exists(Rng(I, 2)) Then Tem = Tem + 1
Next I
GPE = Tem
Set Dic1 = Nothing
Set Dic2 = Nothing
End Function

 

[JinRua]

Cảm ơn anh Ba Tê kết quả thật tuyệt vời!

 

[Hong.Van]

Như vậy dùng SUM cũng được, không cần sumproduct:

=SUM(COUNTIFS($A$2:$A$37,{"A1","A2","A3","A4"},$B$2:$B$37,{"AB";"CD"}))

Cho em hỏi, giả sử có thêm khối C2:C37 và xét thêm điều kiện XX, YY thì hàm COUNTIFS() có tính được không? Em cũng đã thử nhưng chưa được! và kg hiểu nó có mãng mấy chiều?
cthức của em

=SUMPRODUCT((($A$2:$A$37="A1")+($A$2:$A$37="A2")+($A$2:$A$37="A3")+($A$2:$A$37="A4"))*(($B$2:$B$37="AB")+($B$2:$B$37="CD"))*(($C$2:$C$37="XX")+($C$2:$C$37="YY")))

Xin xem File đính kèm. Cảm ơn

 

[Ba Tê]

Cảm ơn anh Ba Tê kết quả thật tuyệt vời!

Chưa tuyệt vời đâu, trong một số trường hợp nào đó nó vẫn bị lỗi.
Nếu muốn sử dụng thì thử lại với cái này xem:

Public Function GPE(Rg1, Rg2, Rg3) As Long
Dim Rng(), Cll As Range, Dic As Object, I As Long, Tem As Long
Set Dic = CreateObject("Scripting.Dictionary")
Rng = Rg1.Value
For Each Cll In Range(Rg2, Rg3)
    If Cll <> "" Then
        If Not Dic.Exists(Cll) Then Dic.Add (Cll), ""
    End If
Next
For I = 1 To UBound(Rng, 1)
    If Dic.Exists(Rng(I, 1)) And Dic.Exists(Rng(I, 2)) Then Tem = Tem + 1
Next I
GPE = Tem
Set Dic = Nothing
End Function

Bỏ bớt 1 Dic.
Sử dụng công thức như cũ.

 

[JinRua]

Bỏ bớt 1 Dic.
Sử dụng công thức như cũ.

Sao em sửa thì cod báo lỗi tô đỏ hết anh à
- Anh xem nếu thêm một cột dữ liệu nữa thì phải sửa thế nào ạ. Ví dụ thêm điều kiện cột "C" có hoặc không kí tự.

 

[Ba Tê]

Sao em sửa thì cod báo lỗi tô đỏ hết anh à
- Anh xem nếu thêm một cột dữ liệu nữa thì phải sửa thế nào ạ. Ví dụ thêm điều kiện cột "C" có hoặc không kí tự.

- Khi muốn hỏi lý do tại sao... gì gì đó thì đưa file "chứng minh" lên mọi người mới biết tại sao.
- Thêm điều kiện gì phải rõ ràng, không thể viết code kiểu "có hoặc không ký tự", ai hiểu nó là sao mà trả lời
- File mà tôi sửa code đây, không thấy lỗi.

 

[JinRua]

- Thêm điều kiện gì phải rõ ràng, không thể viết code kiểu "có hoặc không ký tự", ai hiểu nó là sao mà trả lời

Vâng em gửi File đây nhờ anh chỉ bảo.

 

[Ba Tê]

Vâng em gửi File đây nhờ anh chỉ bảo.

Cứ hỏi lắt nhắt hoài, làm luôn vài chục cột dữ liệu, vài chục dòng điều kiện luôn cho xong. Trong công thức chỉ còn 2 vùng, 1 vùng dữ liệu, 1 vùng điều kiện.

 

[JinRua]

Cứ hỏi lắt nhắt hoài, làm luôn vài chục cột dữ liệu, vài chục dòng điều kiện luôn cho xong. Trong công thức chỉ còn 2 vùng, 1 vùng dữ liệu, 1 vùng điều kiện.

Thành thật cảm ơn thầy Ba Tê. Thực ra em có một bảng báo cáo thống kê hàng tháng em muốn tự động cập nhật, đến ngày báo cáo cứ vậy gửi đi là xong. bản BC gồm thống kê từng đơn vị theo trình độ học vấn nghề nghiệp, giới tính. Em đang dùng kết quả từ bảng Pivot Tabl nhưng thấy nó hơi phiền phức cứ phải đặt bẫy vào công thức không thì nó báo lỗi (ví dụ tháng này ở đơn vị 1 có 1 nữ kỹ sư, tháng sau không có thì công thức báo lỗi )
Em thấy bài #32 phù hợp nhưng dữ liệu cần 3 cột (=GPE(Rng,Rng,Rng,Rng)). Bài #34 nếu thực hiện thì nhiều mảng điều kiện quá (mỗi cell trong báo cáo cần một mảng điều kiện).
Thật phiền thầy quá.

 

[ptm0412]

Cho em hỏi, giả sử có thêm khối C2:C37 và xét thêm điều kiện XX, YY thì hàm COUNTIFS() có tính được không? Em cũng đã thử nhưng chưa được! và kg hiểu nó có mãng mấy chiều?
cthức của em

=SUMPRODUCT((($A$2:$A$37="A1")+($A$2:$A$37="A2")+($A$2:$A$37="A3")+($A$2:$A$37="A4"))*(($B$2:$B$37="AB")+($B$2:$B$37="CD"))*(($C$2:$C$37="XX")+($C$2:$C$37="YY")))

Xin xem File đính kèm. Cảm ơn

Đó mà mảng 3 chiều. Excel sheet không có mảng 3 chiều, chỉ VBA mới có.