Cần giúp về công thức tính tổng không trùng sau khi filter

[caohuy]

Mình có dữ liệu như sau:
Cột A: Mã dự án (một dự án có thể có nhiều nhân viên tham gia)
Cột B: Nhân viên (một nhân viên có thể tham gia nhiều dự án)
Cột C: Số tiền của từng dự án (nếu 1 dự án có 2 nhân viên tham gia thì tổng tiền ở ô C2 chỉ tính 1 lần)
Các bạn xem file và hướng dẫn mình tạo công thức để:
- tính tổng tiền của tất cả dự án ở ô C2 .Theo ví dụ trên thì chỉ có 4 dự án (nhưng bị trùng lặp nhiều lần), nếu 1 dự án xuất hiện 2 lần thì chỉ tính 1 dự án
- Đồng thời, khi filter nhân viên A tham gia bao nhiêu dự án thì số tiền ở ô C2 cũng thay đổi theo các dự án được hiển thị.
Cảm ơn các bạn.

 

[leonguyenz]

Sao không sử dụng SUBTOTAL nhỉ?

=SUBTOTAL(9,C4:C13)

 

[caohuy]

Sao không sử dụng SUBTOTAL nhỉ?

=SUBTOTAL(9,C4:C13)

Nếu sử dụng công thức như bạn thì khi chưa filter nó sẽ không đúng số tiền của chỉ 4 dự án

 

[leonguyenz]

Nếu sử dụng công thức như bạn thì khi chưa filter nó sẽ không đúng số tiền của chỉ 4 dự án

Tổng số tiền của 4 dự án theo bạn là bao nhiêu, bạn có thể cho ví dụ cụ thể theo vài dự án và vài nhân viên được không?

 

[caohuy]

Tổng số tiền của 4 dự án theo bạn là bao nhiêu, bạn có thể cho ví dụ cụ thể theo vài dự án và vài nhân viên được không?

Trong file excel của mình có ghi số tiền của từng dự án đó bạn (cột C)

 

[leonguyenz]

Công thức bài #2 là tôi đã biết cột C là tiền dự án, bạn thử dùng công thức đó rồi filter theo từng dự án, báo kết quả sai và kết quả đúng lại cho tôi xem?

 

[caohuy]

Công thức bài #2 là tôi đã biết cột C là tiền dự án, bạn thử dùng công thức đó rồi filter theo từng dự án, báo kết quả sai và kết quả đúng lại cho tôi xem?

Bạn không hiểu ý mình. Nếu công thức đó (=subtotal(9;C4:C13)) filter theo từng nhân viên thì đúng, nhưng nếu khi chưa filter hoặc filter theo từng dự án thì lại không đúng (nó sẽ cộng hết số tiền của tất cả dự án kể cả dự án trùng là: 730.000 chứ không của phải cùa 4 dự án: 290.000)

 

[mhung12005]

Bạn không hiểu ý mình. Nếu công thức đó (=subtotal(9;C4:C13)) filter theo từng nhân viên thì đúng, nhưng nếu khi chưa filter hoặc filter theo từng dự án thì lại không đúng (nó sẽ cộng hết số tiền của tất cả dự án kể cả dự án trùng là: 730.000 chứ không của phải cùa 4 dự án: 290.000)

Bạn thử file này xem có đúng ý không nhé.

 

[ndu96081631]

Bạn thử file này xem có đúng ý không nhé.

Công thức trong file chỉ đúng khi filter 1 điều kiện. Trường hợp filter theo điều kiện Nhân viên =B hoặc C thì sai
- Khi chưa filter, kết quả = 290,000
- Khi filter (điều kiện Nhân viên =B hoặc C) thì kết quả = 440,000
Ẹc... Ẹc... filter xong mà kết quả lại lớn hơn chưa filter
--------------
Hoặc khi filter cột Mã hoặc cột Tiền thì kết quả cũng sai luôn

 

[caohuy]

Công thức trong file chỉ đúng khi filter 1 điều kiện. Trường hợp filter theo điều kiện Nhân viên =B hoặc C thì sai
- Khi chưa filter, kết quả = 290,000
- Khi filter (điều kiện Nhân viên =B hoặc C) thì kết quả = 440,000
Ẹc... Ẹc... filter xong mà kết quả lại lớn hơn chưa filter
--------------
Hoặc khi filter cột Mã hoặc cột Tiền thì kết quả cũng sai luôn

Bạn hiểu đúng nhu cầu của mình đó. Bạn có công thức nào giúp mình được không?

 

[ndu96081631]

Bạn hiểu đúng nhu cầu của mình đó. Bạn có công thức nào giúp mình được không?

Bài này dùng SUBTOTAL là đúng rồi, nhưng phải thế này:
1> Đặt 2 name:

Data =OFFSET(Sheet1!$A$4,,,COUNTA(Sheet1!$A$4:$A$1000),)

tmp =IF(SUBTOTAL(103,OFFSET(data,ROW(INDIRECT("1:"&ROWS(data)))-1,,1,)),data,"")

2> Công thức:

=SUM((IF(tmp="",0,MATCH(tmp, tmp,0)=ROW(INDIRECT("1:"&ROWS(data)))))*(OFFSET(data,,2)))

Công thức mảng, gõ xong phải bấm tổ hợp phím Ctrl + Shift + Enter
Xem chi tiết trong file

 

[mhung12005]

Công thức trong file chỉ đúng khi filter 1 điều kiện. Trường hợp filter theo điều kiện Nhân viên =B hoặc C thì sai
- Khi chưa filter, kết quả = 290,000
- Khi filter (điều kiện Nhân viên =B hoặc C) thì kết quả = 440,000
Ẹc... Ẹc... filter xong mà kết quả lại lớn hơn chưa filter
--------------
Hoặc khi filter cột Mã hoặc cột Tiền thì kết quả cũng sai luôn

Khi chọn điều kiện A hoặc B hoặc C... thì đúng. Nhưng khi chọn điều kiện A và B, B và C, A và C...(cứ chọn cả 2 điều kiện) thì đúng là sai thật.

Em vẫn chưa nghĩ ra cách làm sao cho nó đúng. Mong sư phụ chỉ giáo để học hỏi. Em cảm ơn.

Hic, gửi bài rồi mới thấy sư phụ đã trả lời. Cảm ơn sư phụ.

 

[caohuy]

Bài này dùng SUBTOTAL là đúng rồi, nhưng phải thế này:
1> Đặt 2 name:

Data =OFFSET(Sheet1!$A$4,,,COUNTA(Sheet1!$A$4:$A$1000),)

tmp =IF(SUBTOTAL(103,OFFSET(data,ROW(INDIRECT("1:"&ROWS(data)))-1,,1,)),data,"")

2> Công thức:

=SUM((IF(tmp="",0,MATCH(tmp, tmp,0)=ROW(INDIRECT("1:"&ROWS(data)))))*(OFFSET(data,,2)))

Công thức mảng, gõ xong phải bấm tổ hợp phím Ctrl + Shift + Enter
Xem chi tiết trong file

Bạn có thể giúp mình thêm là trường hợp nếu trong dữ liệu có hàng rỗng thì công thức thay đổi thế nào không. Cảm ơn bạn.

 

[ndu96081631]

Bạn có thể giúp mình thêm là trường hợp nếu trong dữ liệu có hàng rỗng thì công thức thay đổi thế nào không. Cảm ơn bạn.

Sửa name Data lại thành vầy đi:

Data =$A$4:$A$1000

 

[mhung12005]

Bạn có thể giúp mình thêm là trường hợp nếu trong dữ liệu có hàng rỗng thì công thức thay đổi thế nào không. Cảm ơn bạn.

Bạn đặt lại name data thành công thức này :

=OFFSET(Sheet1!$A$4,,,MATCH(REPT("z",255),Sheet1!$A$4:$A$1000),)

 

[caohuy]

Sửa name Data lại thành vầy đi:

Data =$A$4:$A$1000

Cảm ơn bạn nhiều. Với công thức này thì mình đã có thể giải quyết công việc của mình.
Bạn có thể giải thích giùm mình về sự thay đổi công thức giữa 2 data không. Do mình thấy công thức trong data lúc đầu nó phức tạp hơn công thức trong data lúc sau, mặc dù lúc sau lại có thêm điều kiện có chứa ô rỗng. Kiến thức mình hơi ít nên không hiểu được. Bạn giải thích giùm nha.

 

[caohuy]

Bạn cho mình hỏi nếu bài toán này chỉ yêu cầu tính tổng không trùng (không có filter) thì mình dùng công thức:
=SUMPRODUCT((1/COUNTIF($A$4:$A$13;$A$4:$A$13))*$C$4:$C$13)
Nhưng với dữ liệu có hàng rỗng thì điều chỉnh công thức này như thế nào vậy bạn?

 

[ndu96081631]

Bạn cho mình hỏi nếu bài toán này chỉ yêu cầu tính tổng không trùng (không có filter) thì mình dùng công thức:
=SUMPRODUCT((1/COUNTIF($A$4:$A$13;$A$4:$A$13))*$C$4:$C$13)
Nhưng với dữ liệu có hàng rỗng thì điều chỉnh công thức này như thế nào vậy bạn?

Công thức này:

=SUM(IF($A$4:$A$13<>"",$C$4:$C$13/COUNTIF($A$4:$A$13,$A$4:$A$13)))

Bấm Ctrl + Shift + Enter để kết thúc nhé

 

[caohuy]

Công thức này:

=SUM(IF($A$4:$A$13<>"",$C$4:$C$13/COUNTIF($A$4:$A$13,$A$4:$A$13)))

Bấm Ctrl + Shift + Enter để kết thúc nhé

Có thể dùng hàm sumproduct được không vậy bạn?

 

[ndu96081631]

Có thể dùng hàm sumproduct được không vậy bạn?

Thì cứ thử đi sẽ biết
(dù SUMPRODUCT cũng phải bấm Ctrl + Shift + Enter thôi... Vậy SUM cho nó ngắn)