addictedtonumbers
New Member
- Joined
- Mar 15, 2017
- Messages
- 3
I have a question on how to approach the below problem.
Let's say I have monthly sales data in the array B5:E9. The columns are the total sales in a month, noting the month is indicated by the first date of each month. The rows are Black sales and Blue sales which are product types.
I want to have a formula that answers this question:
For months earlier than the reference date of 31/03/2019 in cell B1, how many blue sales were there? The sum is the sum of the cells indicated in red below. The idea is that if cell B1 changes, then the answer will also change.
I suspect the answer will be a mixture of a SUM/SUMIF/OFFSET formula, maybe INDEX(MATCH) or a SUMPRODUCT? I'm comfortable with these formulae, but get stuck when I have to combine them.
Thank you for your help in advance
Let's say I have monthly sales data in the array B5:E9. The columns are the total sales in a month, noting the month is indicated by the first date of each month. The rows are Black sales and Blue sales which are product types.
I want to have a formula that answers this question:
For months earlier than the reference date of 31/03/2019 in cell B1, how many blue sales were there? The sum is the sum of the cells indicated in red below. The idea is that if cell B1 changes, then the answer will also change.
I suspect the answer will be a mixture of a SUM/SUMIF/OFFSET formula, maybe INDEX(MATCH) or a SUMPRODUCT? I'm comfortable with these formulae, but get stuck when I have to combine them.
Thank you for your help in advance
A | B | C | D | E | |
1 | Reference date | 31/03/2019 | |||
2 | |||||
3 | |||||
4 | 01/01/2019 | 01/02/2019 | 01/03/2019 | 01/04/2019 | |
5 | Black | 1 | 6 | 11 | 16 |
6 | Blue | 2 | 7 | 12 | 17 |
7 | Black | 3 | 8 | 13 | 18 |
8 | Black | 4 | 9 | 14 | 19 |
9 | Blue | 5 | 10 | 15 | 20 |