Guy below you can see i have excel sheet (2016) were i am posting day to day purchases . Each it kept in a particular location/ boxes on each posting. Now i need a formula to index all items(cell no I8 to below) which in particular a box (cell no h7) with total qty purchased in that box . Hope you understand and help me with the formula
Test store.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Date of Purchase | Item purchased | qty | Location | |||||||||
2 | 5/4/2022 | Item A | 2 | Box 1 | |||||||||
3 | 5/5/2022 | Item B | 1 | Box 2 | |||||||||
4 | 5/6/2022 | Item C | 1 | Box 2 | |||||||||
5 | 5/7/2022 | Item B | 2 | Box 1 | |||||||||
6 | 5/8/2022 | Item D | 1 | Box 3 | |||||||||
7 | 5/9/2022 | Item A | 2 | Box 4 | Location : | Box 1 | Item List | Total qty | |||||
8 | 5/10/2022 | Item E | 1 | Box 1 | ? | ? | |||||||
9 | 5/11/2022 | Item E | 1 | Box 3 | ? | ? | |||||||
10 | 5/12/2022 | Item D | 3 | Box 4 | ? | ? | |||||||
11 | 5/13/2022 | Item C | 1 | Box 1 | ? | ? | |||||||
12 | 5/14/2022 | Item B | 5 | Box 1 | ? | ? | |||||||
13 | 5/15/2022 | Item D | 1 | Box 2 | ? | ? | |||||||
14 | 5/16/2022 | Item A | 1 | Box 2 | ? | ? | |||||||
15 | 5/17/2022 | Item E | 2 | Box 3 | ? | ? | |||||||
16 | 5/18/2022 | Item B | 6 | Box 4 | ? | ? | |||||||
17 | 5/19/2022 | Item D | 1 | Box 1 | ? | ? | |||||||
18 | 5/20/2022 | Item C | 1 | Box 2 | ? | ? | |||||||
19 | 5/21/2022 | Item B | 4 | Box 4 | ? | ? | |||||||
20 | 5/22/2022 | Item D | 1 | Box 3 | ? | ? | |||||||
21 | ? | ? | |||||||||||
22 | |||||||||||||
Sheet1 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
H7 | List | =$M$2:$M$5 |