Tìm tổng 2 chiều

[anhquangphan]

em có bài toán này cần mọi người giúp.

Dữ liệu như bên dưới :[TABLE="width: 503"]
[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C [/TD]
[TD] D[/TD]
[TD]E[/TD]
[TD]F [/TD]
[TD] G[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]01-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]1,000[/TD]
[TD]800[/TD]
[TD]0.95[/TD]
[TD="align: right"]12.75[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]01-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]500[/TD]
[TD]300[/TD]
[TD]0.57[/TD]
[TD="align: right"]11.96[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]01-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]650[/TD]
[TD]400[/TD]
[TD]0.77[/TD]
[TD="align: right"]17.00[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]01-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]2,400[/TD]
[TD]1,200[/TD]
[TD]1.15[/TD]
[TD="align: right"]4.03[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]02-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]1,000[/TD]
[TD]800[/TD]
[TD]0.95[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]02-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]500[/TD]
[TD]300[/TD]
[TD]0.57[/TD]
[TD="align: right"]22.00[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]02-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]650[/TD]
[TD]400[/TD]
[TD]0.77[/TD]
[TD="align: right"]11.00[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]02-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]2,400[/TD]
[TD]1,200[/TD]
[TD]1.15[/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]28-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]1,000[/TD]
[TD]800[/TD]
[TD]0.95[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]29-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]500[/TD]
[TD]300[/TD]
[TD]0.57[/TD]
[TD="align: right"]22.00[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]30-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]650[/TD]
[TD]400[/TD]
[TD]0.77[/TD]
[TD="align: right"]11.00[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]31-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]2,400[/TD]
[TD]1,200[/TD]
[TD]1.15[/TD]
[TD="align: right"]10.00
[/TD]
[/TR]
[/TABLE]
[TABLE="width: 432"]
[TR]
[TD="class: xl64, width: 72"][/TD]
[TD="class: xl65, width: 72, align: right"][/TD]
[TD="class: xl66, width: 72"][/TD]
[TD="class: xl67, width: 72"][/TD]
[TD="class: xl68, width: 72, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"]

=> Giờ em muốn như thế này.[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][TABLE="width: 432"]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.57[/TD]
[TD="align: right"]0.77[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]1.15[/TD]
[/TR]
[TR]
[TD="align: left"]

[/TD]
[TD]01-Oct-14[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"]12.75[/TD]
[TD="align: right"]4.03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]02-Oct-14[/TD]
[TD="align: right"]22.00[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]33.00[/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]28-Oct-14[/TD]
[TD] ....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]29-Oct-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30-Oct-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Oct-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]
[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Chi tiết mọi người xem link
http://www.mediafire.com/view/q7iwugu9flm59i9/Book1.xls
[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[/TABLE]

 

[mhung12005]

em có bài toán này cần mọi người giúp.

Dữ liệu như bên dưới :[TABLE="width: 503"]
[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD] D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD] G[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]01-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]1,000[/TD]
[TD]800[/TD]
[TD]0.95[/TD]
[TD="align: right"]12.75[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]01-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]500[/TD]
[TD]300[/TD]
[TD]0.57[/TD]
[TD="align: right"]11.96[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]01-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]650[/TD]
[TD]400[/TD]
[TD]0.77[/TD]
[TD="align: right"]17.00[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]01-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]2,400[/TD]
[TD]1,200[/TD]
[TD]1.15[/TD]
[TD="align: right"]4.03[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]02-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]1,000[/TD]
[TD]800[/TD]
[TD]0.95[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]02-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]500[/TD]
[TD]300[/TD]
[TD]0.57[/TD]
[TD="align: right"]22.00[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]02-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]650[/TD]
[TD]400[/TD]
[TD]0.77[/TD]
[TD="align: right"]11.00[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]02-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]2,400[/TD]
[TD]1,200[/TD]
[TD]1.15[/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]28-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]1,000[/TD]
[TD]800[/TD]
[TD]0.95[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]29-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]500[/TD]
[TD]300[/TD]
[TD]0.57[/TD]
[TD="align: right"]22.00[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]30-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]650[/TD]
[TD]400[/TD]
[TD]0.77[/TD]
[TD="align: right"]11.00[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]31-Oct-14[/TD]
[TD]SA duct[/TD]
[TD="align: right"]2,400[/TD]
[TD]1,200[/TD]
[TD]1.15[/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[/TABLE]
[TABLE="width: 432"]
[TR]
[TD="class: xl64, width: 72"][/TD]
[TD="class: xl65, width: 72, align: right"][/TD]
[TD="class: xl66, width: 72"][/TD]
[TD="class: xl67, width: 72"][/TD]
[TD="class: xl68, width: 72, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"]

=> Giờ em muốn như thế này.[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][TABLE="width: 432"]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.57[/TD]
[TD="align: right"]0.77[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]1.15[/TD]
[/TR]
[TR]
[TD="align: left"]

[/TD]
[TD]01-Oct-14[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"]12.75[/TD]
[TD="align: right"]4.03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]02-Oct-14[/TD]
[TD="align: right"]22.00[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]33.00[/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]28-Oct-14[/TD]
[TD] ....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]29-Oct-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30-Oct-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Oct-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]
[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Chi tiết mọi người xem link
http://www.mediafire.com/view/q7iwugu9flm59i9/Book1.xls[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65, align: right"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[/TR]
[/TABLE]

Bạn dùng công thức này:

=SUMPRODUCT((($B$8:$B$23=$I4)*($F$8:$F$23=J$3)),$G$8:$G$23)

 

[anhquangphan]

Bạn dùng công thức này:

=SUMPRODUCT((($B$8:$B$23=$I4)*($F$8:$F$23=J$3)),$G$8:$G$23)

ko cần thế này em tính được rồi

=SUMIFS('8'!$O$12:$O$447,'8'!$B$12:$B$447,$A2,'8'!$M$12:$M$447,B$1)