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

roelandwatteeuw

Board Regular
Joined
Feb 20, 2015
Messages
88
Office Version
  1. 365
Platform
  1. 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).

Product Number01/01/202401/02/202401/03/2024
28923003
28950010
28978000
28978100
28978290
289780110
289780015

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

ProductNrMonth
2897801/01/2024
3142801/01/2024
2892301/01/2024
2895001/01/2024
2892301/02/2024
2897801/02/2024
2892301/03/2024
2895001/03/2024
2897801/03/2024


ProductNr is in Named Range 'rngProductNr' = Green (incl. headers).
Month is in Named Range 'rng
Month' = 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:

ProductNrMonthMonth Amount
2897801/01/20243 (0 + 1 + 2 + 0 + 0)
3142801/01/20240
2892301/01/20240
2895001/01/20240
2892301/02/20240
2897801/02/202420 (0 + 0 + 9 + 11 + 0 )
2892301/03/20243
2895001/03/20240
2897801/03/202415 (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!
 
Perhaps something along these lines:
Excel Formula:
=MAP(rngProductNr; rng_Month; LAMBDA(n;m; SUMIFS(XLOOKUP(m; RngPrmDates; RngCalDays); RngProdNr; n)))
-OR-
Excel Formula:
=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).
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top