SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện

[vanthinh3101]

Chào ACE,

Mình có CT như file đính kèm, nhờ các cao thủ chỉ giáo làm sao để ra kết quả ở D10. Vì CT ở C10 ra không đúng ý mình.

Nếu theo công thức bạn viết ở ô D10 thì tôi hiểu rằng bạn không cần điều kiện ở ô A9, A10.
Như vậy thì có thể không xét điều kiện tại A9, A10 và dùng Sumif

C9=SUM(SUMIF($B$2:$B$7,IF($F$2:$F$4=B9,$G$2:$G$4),$C$2:$C$7))

Ctrl + Shift + Enter, Fill xuống

 

[tieuthubuongbinh]

Thử sửa công thức tại C9:

=SUMPRODUCT(SUMIFS($C$2:$C$7,$B$2:$B$7,TRANSPOSE(OFFSET($G$1,MATCH($B9,$F$2:$F$4,0),,COUNTIF($F$2:$F$4,B9))),$A$2:$A$7,$A$9:$A$10))

Ctrl+Shift+Enter, kéo xuống C10.

Thêm cách nữa:

C9=SUM(SUMIFS($C$2:$C$7,$A$2:$A$7,TRANSPOSE($A$9:$A$10),$B$2:$B$7,IF(B9=$F$2:$F$4,$G$2:$G$4)))

Kết thúc bằng Ctrl+Shift+Enter, fill xuống 1 dòng.

Thân

Em được bạn bày thêm cách này:

C9=SUMPRODUCT(--(LOOKUP($B$2:$B$7,$G$2:$G$4,$F$2:$F$4)=$B9),$C$2:$C$7)

 

[tieuthubuongbinh]

Nếu theo công thức bạn viết ở ô D10 thì tôi hiểu rằng bạn không cần điều kiện ở ô A9, A10.
Như vậy thì có thể không xét điều kiện tại A9, A10 và dùng Sumif

C9=SUM(SUMIF($B$2:$B$7,IF($F$2:$F$4=B9,$G$2:$G$4),$C$2:$C$7))

Ctrl + Shift + Enter, Fill xuống

Do em sơ xuất đấy bác, thực ra e muốn có xét cột A nhưng lúc đưa file lên em lại quên tính đến cột A. Nhưng vô tình em lại học được cách sumifs kết hợp transpose. Cách của bác @Phan Thế Hiệp giúp em tận dụng được cả 2 phương án.

Khi cần xét thêm cột A thì em thay hàm transpose là xong

Em rất cám ơn các bác ạ.

 

[Phan Thế Hiệp]

Do em sơ xuất đấy bác, thực ra e muốn có xét cột A nhưng lúc đưa file lên em lại quên tính đến cột A. Nhưng vô tình em lại học được cách sumifs kết hợp transpose. Cách của bác @Phan Thế Hiệp giúp em tận dụng được cả 2 phương án.

Khi cần xét thêm cột A thì em thay hàm transpose là xong

Em rất cám ơn các bác ạ.

View attachment 213877

Tôi lại hiểu có xét và không xét Vùng A9:A10 như trong file kèm chứ!

Thân

 

[tieuthubuongbinh]

Tôi lại hiểu có xét và không xét Vùng A9:A10 như trong file kèm chứ!

Thân

CT của bác em hiểu là sum(sumifs của A9,Sumifs của A10)=190, bác bỏ sum ra thì là 100 ấy, và nếu xét thì 100 mới đúng bác ạ

 C9=SUMIFS($C$2:$C$7,$A$2:$A$7,TRANSPOSE($A$9:$A$10),$B$2:$B$7,IF(B9=$F$2:$F$4,$G$2:$G$4))

 

[lathanhtre]

các anh cho em hỏi có các nào cộng tổng với tình huống thế này:

	a​	b​
1​	01/02/2013​	8T3→001
2​	02/02/2013​	8T3→001
3​	03/02/2013​	8T3→001
4​	04/02/2013​	8T3→004
	tổng​

nếu cộng thủ công thì tổng là 7.
Cách của em làm là em phải làm ra thêm 1 cột nữa để lấy số sau đó mới cộng lại. Vậy em muốn hỏi là có công thức nào cộng mà không cần phải thêm cột ko ạ.
thanks

 

[Phan Thế Hiệp]

các anh cho em hỏi có các nào cộng tổng với tình huống thế này:

	a​	b​
1​	01/02/2013​	8T3→001
2​	02/02/2013​	8T3→001
3​	03/02/2013​	8T3→001
4​	04/02/2013​	8T3→004
	tổng​

nếu cộng thủ công thì tổng là 7.


Cách của em làm là em phải làm ra thêm 1 cột nữa để lấy số sau đó mới cộng lại. Vậy em muốn hỏi là có công thức nào cộng mà không cần phải thêm cột ko ạ.
Cảm ơn

Không cần thêm cột gì ráo, chỉ cần:

=SUM(A1:A5) =10, lận! sao bằng 7?

Thân

 

[lathanhtre]

Không cần thêm cột gì ráo, chỉ cần:

=SUM(A1:A5) =10, lận! sao bằng 7?

Thân

ý là muốn cộng số ở sao dấu"→" á. từ ngày 1 tới ngày 04/02/2013 là được 7 phiếu á
thanks

 

[Phan Thế Hiệp]

ý là muốn cộng số ở sao dấu"→" á. từ ngày 1 tới ngày 04/02/2013 là được 7 phiếu á
Cảm ơn

Dùng:
=SUMPRODUCT(--RIGHT(C2:C5,3)) Enter.

Thân

 

[tieuthubuongbinh]

Dùng:
=SUMPRODUCT(--RIGHT(C2:C5,3)) Enter.

Thân

Bác cho em hỏi -- có nghĩa gì ạ? em rất hay thấy mà ko hiểu

 

[Phan Thế Hiệp]

Bác cho em hỏi -- có nghĩa gì ạ? em rất hay thấy mà ko hiểu

Dựa vào toán học:

• -(-a) = --a = a
• Vd:
  • =-TEXT(125,"0")=-125
  • =--TEXT(125,"0")=125

Vận dụng điều này để "ép" a đang là dạng "chuỗi số" (Vd: chuỗi '12345) về thành số tính toán (12,345).
Có nhiều cách đưa "chuỗi số" về dạng số, gồm:

1. VALUE( "chuỗi số" a )
2. "chuỗi số" a nhân hay chia 1 (*1 ; /1), hoặc cộng hay trừ (+0 ; -0)
3. "chuỗi số" a cộng/trừ/nhân/chia/căn/lũy thừa với một "chuỗi số" khác.
   • Vd: =TEXT(10,"0")^TEXT(3,"0")=1000

Thân

 

[lathanhtre]

Dùng:
=SUMPRODUCT(--RIGHT(C2:C5,3)) Enter.

Thân

thanks anh nhiều ạ, mình làm được rồi.

 

[echo07]

Chào các bạn. Tôi cần tính tổng theo nhiều điều kiện như file đính kèm, cũng đã sử dụng công thức mảng nhưng không ra được kết quả như mong muốn. Rất mong anh em giúp đỡ. Xin cảm ơn trước. Nội dung cần trợ giúp đã ghi rõ trong file.

 

[Phan Thế Hiệp]

Chào các bạn. Tôi cần tính tổng theo nhiều điều kiện như file đính kèm, cũng đã sử dụng công thức mảng nhưng không ra được kết quả như mong muốn. Rất mong anh em giúp đỡ. Xin cảm ơn trước. Nội dung cần trợ giúp đã ghi rõ trong file.

Bạn giải thích cách tính toán chi tiết thêm:

1. Ngày 08/04/19 tại sao là 3
2. Ngày 22/04/19 tại sao là 8
3. Ngày 06/05/19 tại sao là 10
4. Ngày 29/07/19 tại sao là 7

Thân

 

[echo07]

Bạn giải thích cách tính toán chi tiết thêm:

1. Ngày 08/04/19 tại sao là 3 -->Em cần 3 người làm trong 47 ngày cho đầu mục công việc số 1.(cột số ngày LV) bắt đầu từ ngày 8/4 đến ngày 31/5. Do đó tất cả các ngày làm việc trong khoảng thời gian này đều bố trí 3 người. Các đầu việc khác không bố trí cùng thời gian này nên kết quả là 3.
2. Ngày 22/04/19 tại sao là 8 -- Em cộng tất cả các đầu việc lại, tương tự cho các ngày khác.
3. Ngày 06/05/19 tại sao là 10
4. Ngày 29/07/19 tại sao là 7

Thân

 

[Phan Thế Hiệp]

Ngày 08/04/19 tại sao là 3 -->Em cần 3 người làm trong 47 ngày (cột số ngày LV) bắt đầu từ ngày 8/4 đến ngày 31/5. Do đó tất cả các ngày làm việc trong khoảng thời gian này đều bố trí 3 người.

1. Ngày 22/04/19 tại sao là 8 -- Em cộng tất cả các đầu việc lại, tương tự cho các ngày khác.
2. Ngày 06/05/19 tại sao là 10
3. Ngày 29/07/19 tại sao là 7

À! tôi hiểu ý bạn rồi, phải xem cả bảng thời gian "bắt đầu - kết thúc" và vì nó không sắp thứ tự theo ngày nên ban đầu tôi nhầm chút.
Thử công thức sau:

H19=SUMPRODUCT((MMULT(COUNTIF(H$18,">="&$E$3:$F$8+{0,1}),{1;1})=1)*INDEX($B$3:$D$8,,MATCH($G19,$B$2:$D$2,)))

Enter, fill qua phải, rồi copy cả dòng xuống

Thân

 

[echo07]

Bạn trả lời chung chung quá đi!
Ví dụ: Ngày 22/04/19: tôi lấy B3=3 (từ ngày 08/04 đến mốc này), cộng B4=3 (từ ngày 20/04 đến mốc này) chỉ bằng 6 thôi mà???? sao bạn lại tính 8. ---> Em tính nháp cho phần Nhân công 1 trong phần bên trên đó ạ. Ngày 22/4 cần 8 người cho các đầu việc sau: Mục 1 =3 (ô i3), Mục 2 = 3 (ô i4) Mục 4 bằng 2 (ô i6) như vậy sum lại ngày đó là cần 8 người.

Giải thích càng chi tiết thì người giải mới biết bạn muốn tính như thế nào là đúng chứ!?

Thân

 

[Phan Thế Hiệp]

Đã giải cho bạn ở trên rồi đó.

Thân

 

[echo07]

Đã giải cho bạn ở trên rồi đó.

Thân

Vâng, cảm ơn bác, quá hay ạ. Em lại phải nghiên cứu thêm cái hàm MMULT này mới được.

 

[Phan Thế Hiệp]

Em lại phải nghiên cứu thêm cái hàm MMULT này mới được.

Đây, cho bạn "ngâm cứu" về "người đẹp" ấy.

Thân