xinyan1129
New Member
- Joined
- Feb 22, 2017
- Messages
- 11
[TABLE="width: 1016"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Month[/TD]
[TD]Customer name[/TD]
[TD]Channel[/TD]
[TD]Gross sales[/TD]
[TD]Return [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Current Month[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May-17[/TD]
[TD]ABC BOOKSTORE[/TD]
[TD]BOOKSTORE[/TD]
[TD]8,035 [/TD]
[TD](788)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Channel[/TD]
[TD]Gross sales[/TD]
[TD]Return [/TD]
[TD][/TD]
[TD]May-17[/TD]
[TD]VENICE RUNNER[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD]36,103 [/TD]
[TD](37,861)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BOOKSTORE[/TD]
[TD]50,143 [/TD]
[TD](788)[/TD]
[TD][/TD]
[TD]May-17[/TD]
[TD]PGSM BOOKSTORE[/TD]
[TD]BOOKSTORE[/TD]
[TD]42,108 [/TD]
[TD]0 [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD]51,446 [/TD]
[TD](37,861)[/TD]
[TD][/TD]
[TD]May-17[/TD]
[TD]VENTI RUNNER[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD]15,343 [/TD]
[TD]0 [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun-17[/TD]
[TD]VC STATIONERY & BOOK[/TD]
[TD]BOOKSTORE[/TD]
[TD]2,610 [/TD]
[TD]0 [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun-17[/TD]
[TD]FABER LINK[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD]76 [/TD]
[TD]0 [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="colspan: 2"]Year to date (May'17 - Jun'17)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Channel[/TD]
[TD]Gross sales[/TD]
[TD]Return [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
If I wish to search gross sales and return amount for current month (May’17), so formula in Cell B5
=SUMPRODUCT((MONTH($F$3:$F$8)=$B$1)*(YEAR($F$3:$F$8)=$C$1)*($G$3:$G$8="BOOKSTORE")*($I$3:$I$8))
What if I wish to search gross sales and return amount for Year to date (May’17 – Jun’17), which formula I should insert in cell B11?
Please help and thanks in advance.
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Month[/TD]
[TD]Customer name[/TD]
[TD]Channel[/TD]
[TD]Gross sales[/TD]
[TD]Return [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Current Month[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May-17[/TD]
[TD]ABC BOOKSTORE[/TD]
[TD]BOOKSTORE[/TD]
[TD]8,035 [/TD]
[TD](788)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Channel[/TD]
[TD]Gross sales[/TD]
[TD]Return [/TD]
[TD][/TD]
[TD]May-17[/TD]
[TD]VENICE RUNNER[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD]36,103 [/TD]
[TD](37,861)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BOOKSTORE[/TD]
[TD]50,143 [/TD]
[TD](788)[/TD]
[TD][/TD]
[TD]May-17[/TD]
[TD]PGSM BOOKSTORE[/TD]
[TD]BOOKSTORE[/TD]
[TD]42,108 [/TD]
[TD]0 [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD]51,446 [/TD]
[TD](37,861)[/TD]
[TD][/TD]
[TD]May-17[/TD]
[TD]VENTI RUNNER[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD]15,343 [/TD]
[TD]0 [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun-17[/TD]
[TD]VC STATIONERY & BOOK[/TD]
[TD]BOOKSTORE[/TD]
[TD]2,610 [/TD]
[TD]0 [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun-17[/TD]
[TD]FABER LINK[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD]76 [/TD]
[TD]0 [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="colspan: 2"]Year to date (May'17 - Jun'17)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Channel[/TD]
[TD]Gross sales[/TD]
[TD]Return [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]SCHOOL RUNNER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
If I wish to search gross sales and return amount for current month (May’17), so formula in Cell B5
=SUMPRODUCT((MONTH($F$3:$F$8)=$B$1)*(YEAR($F$3:$F$8)=$C$1)*($G$3:$G$8="BOOKSTORE")*($I$3:$I$8))
What if I wish to search gross sales and return amount for Year to date (May’17 – Jun’17), which formula I should insert in cell B11?
Please help and thanks in advance.