# Summing values based on increasing date + additional criteria

1. ## Summing values based on increasing date + additional criteria

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!   Register To Reply

2. ## Re: Summing values based on increasing date + additional criteria

Hi Louise

Your function returns an array of 3 items and not a scalar value. To get the total of the array, you have to nest your formula with the SUM function as shown below:

=SUM(SUMIFS(D2:D20,C2:C20,SMALL(IF(B2:B20=40730,C2:C20),{1;2;3}),B2:B20,40730))

You dont need to Ctrl+Shift+Enter  Register To Reply

3. ## Re: Summing values based on increasing date + additional criteria

I3=SUM(IF(B2:B20=B11,IF(C2:C20<=SMALL(IF(B2:B20=B11,C2:C20),3),D2:D20)))

Control+shift+enter  Register To Reply

4. ## Re: Summing values based on increasing date + additional criteria Originally Posted by CARACALLA I3=SUM(IF(B2:B20=B11,IF(C2:C20<=SMALL(IF(B2:B20=B11,C2:C20),3),D2:D20)))

Control+shift+enter
This formula will result in an error if the number of transactions is less than 3  Register To Reply

5. ## Re: Summing values based on increasing date + additional criteria Originally Posted by t.gatheru Hi Louise

Your function returns an array of 3 items and not a scalar value. To get the total of the array, you have to nest your formula with the SUM function as shown below:

=SUM(SUMIFS(D2:D20,C2:C20,SMALL(IF(B2:B20=40730,C2:C20),{1;2;3}),B2:B20,40730))

You dont need to Ctrl+Shift+Enter
Thank you! I was under the impression that the SUMIFS() would sum the values on its own. However, your formula only worked when I used Ctrl+Shift+Enter. It returned 0 otherwise.  Register To Reply

6. ## Re: Summing values based on increasing date + additional criteria Originally Posted by CARACALLA I3=SUM(IF(B2:B20=B11,IF(C2:C20<=SMALL(IF(B2:B20=B11,C2:C20),3),D2:D20)))

Control+shift+enter
Thank you, this worked as well!  Register To Reply

7. ## Re: Summing values based on increasing date + additional criteria Originally Posted by t.gatheru This formula will result in an error if the number of transactions is less than 3
I see, but I actually know the exact amount of transactions so I don't think it will be a problem.  Register To Reply

8. ## Re: Summing values based on increasing date + additional criteria Originally Posted by Louise Alice Thank you! I was under the impression that the SUMIFS() would sum the values on its own. However, your formula only worked when I used Ctrl+Shift+Enter. It returned 0 otherwise.
I am using Office 365. Maybe that is the reason the formula works in my computer without Ctrl+Shift+Enter  Register To Reply

9. ## Re: Summing values based on increasing date + additional criteria Originally Posted by t.gatheru I am using Office 365. Maybe that is the reason the formula works in my computer without Ctrl+Shift+Enter
Yes, perhaps. To extend on my problem: I first summed the 3 first values for a certain customer. Now, I want to sum the 2 last.

I try: {=SUM(SUMIFS(D2:D20;C2:C20;LARGE(IF(B2:B20=40730;C2:C20);{1;2});B2:B20;40730))} and I want it to return 269 608.

However, it seems that since the 4th value for this customer is 0, the formula adds the 5th number twice, and my result is 539 216. Is there any way I can get around this?  Register To Reply

10. ## Re: Summing values based on increasing date + additional criteria

I3=SUM(IF(B2:B20=B11,IF(C2:C20>=LARGE(IF(B2:B20=B11,C2:C20),2),D2:D20)))

Control+shift+enter  Register To Reply

11. ## Re: Summing values based on increasing date + additional criteria

Write the delivery date or delivery dates you want to sum in the

yellow column for the costum number you enter in k2

in I3 you will have the sum

I3=SUMPRODUCT(SUMIFS(D2:D20,C2:C20,L2:L100,B2:B20,K2))  Register To Reply

12. ## Re: Summing values based on increasing date + additional criteria Originally Posted by CARACALLA I3=SUM(IF(B2:B20=B11,IF(C2:C20>=LARGE(IF(B2:B20=B11,C2:C20),2),D2:D20)))

Control+shift+enter
Thanks, this worked well. Of course I only tried the other formula I received   Register To Reply

13. ## Re: Summing values based on increasing date + additional criteria Originally Posted by CARACALLA Write the delivery date or delivery dates you want to sum in the

yellow column for the costum number you enter in k2

in I3 you will have the sum

I3=SUMPRODUCT(SUMIFS(D2:D20,C2:C20,L2:L100,B2:B20,K2))
I'm not sure what you're saying I should put in the yellow column - it is a certain amount of dates according to their ascending order I want, such as the 3 or 4 first values according to date. I don't want specific dates. But the previous formulas you gave me work well!   Register To Reply

14. ## Re: Summing values based on increasing date + additional criteria

I encountered some issues when attempting to use your formulas with my larger data set.

The problem now is that I have several orders with the same delivery date. This is where t.gatheru your formula fails when I attempt to get the 2 last numbers in my example sheet, because the 2 last orders have the same delivery dates and so Excel apparently chooses 269 608 twice.

CARACALLA your formula fails due to this reason as well, because many of my orders have the same delivery date, see example in attached sheet. The formula simply adds all the values for customer 40730, since the dates are all the same.

Any thoughts on this?  Register To Reply

15. ## Re: Summing values based on increasing date + additional criteria

=SUM(IF(B2:B20=40730,IF(a2:a20<>"",IF(A2:A20>=LARGE(A2:A20,2),D2:D20))))

control+shift+enter

try with order number  Register To Reply

16. ## Re: Summing values based on increasing date + additional criteria Originally Posted by CARACALLA =SUM(IF(B2:B20=40730,IF(a2:a20<>"",IF(A2:A20>=LARGE(A2:A20,2),D2:D20))))

control+shift+enter

try with order number
Thanks for your suggestion, but now this formula does not include the delivery date. I have a large data set with multiple delivery dates, and therein multiple order numbers, so both must be taken into account. I guess I have to nest the priotitization of the order number inside the IF() function for the delivery date, but I'm not sure how to yet.

Something like "take the 3 first orders based on delivery date, but if the delivery dates are the same, pick the orders according to their order number". Working on it...  Register To Reply

17. ## Re: Summing values based on increasing date + additional criteria

order number works

Attach a file to try it out  Register To Reply

18. ## Re: Summing values based on increasing date + additional criteria

The formula works independent of the dates because the number

order is different even if the dates are the same

this formula works without comparing dates

=SUM(IF(B2:B20=B11,IF(A2:A20<=SMALL(IF(B2:B20=B11,A2:A20),3),D2:D20)))

cse  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 