Summing values based on increasing date + additional criteria

**Louise Alice** · 06-15-2020, 05:28 AM

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!

**t.gatheru** · 06-15-2020, 06:08 AM

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

**CARACALLA** · 06-15-2020, 06:35 AM

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

Control+shift+enter

**t.gatheru** · 06-15-2020, 06:44 AM

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

**Louise Alice** · 06-15-2020, 08:14 AM

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.

**Louise Alice** · 06-15-2020, 08:16 AM

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!

**Louise Alice** · 06-15-2020, 08:17 AM

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.

**t.gatheru** · 06-15-2020, 08:20 AM

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

**Louise Alice** · 06-15-2020, 08:38 AM

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?

**CARACALLA** · 06-15-2020, 09:03 AM

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

Control+shift+enter

**CARACALLA** · 06-15-2020, 09:37 AM

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

**Louise Alice** · 06-15-2020, 10:09 AM

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

**Louise Alice** · 06-15-2020, 10:23 AM

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!

**Louise Alice** · 06-15-2020, 11:44 AM

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?

**CARACALLA** · 06-15-2020, 12:41 PM

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

control+shift+enter

try with order number

**Louise Alice** · 06-17-2020, 09:14 AM

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

**CARACALLA** · 06-17-2020, 09:19 AM

order number works

Attach a file to try it out

**CARACALLA** · 06-17-2020, 11:49 AM

The formula works independent of the dates because the number

order is different even if the dates are the same

your first request post #6

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

Summing values based on increasing date + additional criteria

LinkBack

Thread Tools

Rate This Thread

Display

Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Re: Summing values based on increasing date + additional criteria

Thread Information

Users Browsing this Thread

Similar Threads

Returning the values of a column, in increasing value, based on one additional criteria

Default Summing values based on criteria

[SOLVED] Sum of values based on values in different column with additional criteria

[SOLVED] Summing Largest 50 values based on criteria

Summing based on date criteria

Summing values based on two criteria

Summing Values Based on Text Criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions