Get sum off values from other table with conditions (with spilled result) - SUMIFS

[roelandwatteeuw]

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).

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'

=MATCH(rng_month; RngPrmDates; 0)

Then I made an INDEX from 'RngCalDays' with those rows.

=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.

=SUMIFS(M2#; RngProdNr; rngProductNr)

But this results in a #VALUE! error

I can't figure it out...
Can you?

Thanks!

 

[djclements]

Perhaps something along these lines:

=MAP(rngProductNr; rng_Month; LAMBDA(n;m; SUMIFS(XLOOKUP(m; RngPrmDates; RngCalDays); RngProdNr; n)))

-OR-

=MMULT((rngProductNr = TOROW(IF({1}; RngProdNr; RngCalDays))) * (rng_Month = TOROW(IF({1}; RngPrmDates; RngCalDays))); --TOCOL(RngCalDays))

NOTE: use DROP(rngProductNr;1) and/or DROP(rng_Month;1) if needed (to excl. headers).

 

[roelandwatteeuw]

Perhaps something along these lines:

=MAP(rngProductNr; rng_Month; LAMBDA(n;m; SUMIFS(XLOOKUP(m; RngPrmDates; RngCalDays); RngProdNr; n)))

-OR-

=MMULT((rngProductNr = TOROW(IF({1}; RngProdNr; RngCalDays))) * (rng_Month = TOROW(IF({1}; RngPrmDates; RngCalDays))); --TOCOL(RngCalDays))

NOTE: use DROP(rngProductNr;1) and/or DROP(rng_Month;1) if needed (to excl. headers).

Hi djclements

Thanks for you time.
Both seem to work, THANKS!

The second one, with the MMULT is a bit more complicated (for me), so I'll stick with the first option.
That one is easy to reproduce.

At first, it didn't seem to work... I got another error.​

In order to map out the problem, I wanted to make a simplified file, so you could look what's possibly wrong.​

And in this file it worked like a charm.
So probably I made some kind of range error (header / no header)​

Happy you could help me this fast!

grtz
Roeland