xinyan1129
New Member
- Joined
- Feb 22, 2017
- Messages
- 11
A | B | C | D | E | F | G | I | J | |
1 | 5 | 2017 | Data | ||||||
2 | Month | Customer name | Channel | Gross sales | Return | ||||
3 | Current Month | May-17 | ABC BOOKSTORE | BOOKSTORE | 8,035 | (788) | |||
4 | Channel | Gross sales | Return | May-17 | VENICE RUNNER | SCHOOL RUNNER | 36,103 | (37,861) | |
5 | BOOKSTORE | 50,143 | (788) | May-17 | PGSM BOOKSTORE | BOOKSTORE | 42,108 | 0 | |
6 | SCHOOL RUNNER | 51,446 | (37,861) | May-17 | VENTI RUNNER | SCHOOL RUNNER | 15,343 | 0 | |
7 | Jun-17 | VC STATIONERY & BOOK | BOOKSTORE | 2,610 | 0 | ||||
8 | Jun-17 | FABER LINK | SCHOOL RUNNER | 76 | 0 | ||||
9 | Year to date (May'17 - Jun'17) | ||||||||
10 | Channel | Gross sales | Return | ||||||
11 | BOOKSTORE | ||||||||
12 | SCHOOL RUNNER |
<tbody>
</tbody>
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.