Hello everyone,
I am new to this forum and will try to explain my problem as well as possible. Thank you very much in advance for your support! For the record, I am not familiar with VBA. Please see the attached spreadsheet as I explain my problem.
In the spreadsheet, you see 19 different orders. They all belong to a certain customer (col B), have a certain delivery date (col C) and a certain invoiced value (col D).
Here is my problem statement: sum the invoiced values (col D) of the k first orders of a certain customer, based on increasing delivery date. The second part is then to sum the n-k last orders with the same criteria, where n is the total amount of orders for that particular customer.
For instance, we have customer number 40730, and I want to know the sum of the three first invoiced value according to their increasing delivery date. When Delivery Date (col C) is sorted oldest to newest, the sum of the first three invoiced values for customer 40730 (col D) is 616 724. The sum of the last two invoiced values if 269 608.
So far, I have managed to create an array with the three smallest dates for a given customer (40730) using the following formula: {=SMALL(IF(B2:B20=40730;C2:C20);{1;2;3})}
But, I fail when trying to incorporate it in a SUMIFS(). My current, full formula looks like this at the moment: {=SUMIFS(D2:D20;C2:C20;SMALL(IF(B2:B20=40730;C2:C20);{1;2;3});B2:B20;40730)}
... but this just returns the three first values in an array and does not sum them, like this: {339932;132971;143821}
Looking forward to learn something new and to get your expert advice! Many thanks!
Bookmarks