roelandwatteeuw
Board Regular
- Joined
- Feb 20, 2015
- Messages
- 88
- Office Version
- 365
- Platform
- Windows
Hi everyone,
Here is a question for true experts who love a challenge.
Unfortunately, I can't figure it out.
I have a list called 'Sales'.
This is contained in a Named Range 'RngCalDays' = Purple (incl. headers).
The list contains (consecutive) months in the columns and sales quantities in the rows.
The top row, with the months, is also placed in a Named Range called 'RngPrmDates = Purple Headers.
In front of each row there is also a Product Number.
The Product Number is also placed in a Named Range called 'RngProdNr' = Red (incl. headers).
As you can see, a product can have multiple lines, as well as quantities in multiple month columns.
i.e.
28978 has 5 lines.
And it has two values in column '01/01/2024', two values in month '01/02/2024', one value in '01/03/2024' and a row with only 0.
Then I have a second (separate) list 'Products'
Mostly it contains more rows than the 'Sales' list
ProductNr is in Named Range 'rngProductNr' = Green (incl. headers).
Month is in Named Range 'rngMonth' = Orange (incl. headers).
In the second list, a third column must now be added, containing the sum of the sales quantities from the range 'RngCalDays'.
The sum should be taken from the quantities that belong to this Product Number and occurred in the month listed in the second column 'Month'.
So:
Note that some Product Numbers may appear multiple times in the 'Products' list.
There may also be Product Numbers in this list that do not appear in the 'Sales' list.
The products and sales are also not in order.
This should be the result of this example:
It is important that the result is spilled!
What have I tried?
First define for each row in 'Products' which Column is needed from 'RngCalDays'. Did this with the MATCH function between 'rng_month' and 'RngPrmDates'.
Then I named this 'RngCalDaysClm'
Then I made an INDEX from 'RngCalDays' with those rows.
This results (in cell M2) is a spilled range, where it looks like every cel contains the values from the asked row.
(Only the first value from 'RngCalDays' is visible = here that's the date.
The rest is hidden behind the cell.
If you select the first cel and use the Function helper button, you'll see this in the Result)
And finally I tried to make the sum with SUMIFS.
But this results in a #VALUE! error
I can't figure it out...
Can you?
Thanks!
Here is a question for true experts who love a challenge.
Unfortunately, I can't figure it out.
I have a list called 'Sales'.
This is contained in a Named Range 'RngCalDays' = Purple (incl. headers).
The list contains (consecutive) months in the columns and sales quantities in the rows.
The top row, with the months, is also placed in a Named Range called 'RngPrmDates = Purple Headers.
In front of each row there is also a Product Number.
The Product Number is also placed in a Named Range called 'RngProdNr' = Red (incl. headers).
Product Number | 01/01/2024 | 01/02/2024 | 01/03/2024 |
---|---|---|---|
28923 | 0 | 0 | 3 |
28950 | 0 | 1 | 0 |
28978 | 0 | 0 | 0 |
28978 | 1 | 0 | 0 |
28978 | 2 | 9 | 0 |
28978 | 0 | 11 | 0 |
28978 | 0 | 0 | 15 |
As you can see, a product can have multiple lines, as well as quantities in multiple month columns.
i.e.
28978 has 5 lines.
And it has two values in column '01/01/2024', two values in month '01/02/2024', one value in '01/03/2024' and a row with only 0.
Then I have a second (separate) list 'Products'
Mostly it contains more rows than the 'Sales' list
ProductNr | Month |
---|---|
28978 | 01/01/2024 |
31428 | 01/01/2024 |
28923 | 01/01/2024 |
28950 | 01/01/2024 |
28923 | 01/02/2024 |
28978 | 01/02/2024 |
28923 | 01/03/2024 |
28950 | 01/03/2024 |
28978 | 01/03/2024 |
ProductNr is in Named Range 'rngProductNr' = Green (incl. headers).
Month is in Named Range 'rngMonth' = Orange (incl. headers).
In the second list, a third column must now be added, containing the sum of the sales quantities from the range 'RngCalDays'.
The sum should be taken from the quantities that belong to this Product Number and occurred in the month listed in the second column 'Month'.
So:
- RngProdNr vs. rngProductNr
- RngPrmDates vs. rng_Month
Note that some Product Numbers may appear multiple times in the 'Products' list.
There may also be Product Numbers in this list that do not appear in the 'Sales' list.
The products and sales are also not in order.
This should be the result of this example:
ProductNr | Month | Month Amount |
---|---|---|
28978 | 01/01/2024 | 3 (0 + 1 + 2 + 0 + 0) |
31428 | 01/01/2024 | 0 |
28923 | 01/01/2024 | 0 |
28950 | 01/01/2024 | 0 |
28923 | 01/02/2024 | 0 |
28978 | 01/02/2024 | 20 (0 + 0 + 9 + 11 + 0 ) |
28923 | 01/03/2024 | 3 |
28950 | 01/03/2024 | 0 |
28978 | 01/03/2024 | 15 (0 + 0 + 0 + 0 + 15) |
It is important that the result is spilled!
What have I tried?
First define for each row in 'Products' which Column is needed from 'RngCalDays'. Did this with the MATCH function between 'rng_month' and 'RngPrmDates'.
Then I named this 'RngCalDaysClm'
Excel Formula:
=MATCH(rng_month; RngPrmDates; 0)
Then I made an INDEX from 'RngCalDays' with those rows.
Excel Formula:
=INDEX(RngCalDays; 0; RngCalDaysClm)
This results (in cell M2) is a spilled range, where it looks like every cel contains the values from the asked row.
(Only the first value from 'RngCalDays' is visible = here that's the date.
The rest is hidden behind the cell.
If you select the first cel and use the Function helper button, you'll see this in the Result)
And finally I tried to make the sum with SUMIFS.
Excel Formula:
=SUMIFS(M2#; RngProdNr; rngProductNr)
But this results in a #VALUE! error
I can't figure it out...
Can you?
Thanks!