+ Reply to Thread
Results 1 to 18 of 18

Summing values based on increasing date + additional criteria

  1. #1
    Registered User
    Join Date
    06-11-2020
    Location
    Stockholm
    MS-Off Ver
    2016
    Posts
    13

    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!
    Attached Files Attached Files
    Last edited by Louise Alice; 06-17-2020 at 09:07 AM. Reason: Not yet solved :(

  2. #2
    Registered User
    Join Date
    02-05-2020
    Location
    Nairobi, Kenya
    MS-Off Ver
    Office 365
    Posts
    14

    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

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    831

    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

  4. #4
    Registered User
    Join Date
    02-05-2020
    Location
    Nairobi, Kenya
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Summing values based on increasing date + additional criteria

    Quote Originally Posted by CARACALLA View Post
    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

  5. #5
    Registered User
    Join Date
    06-11-2020
    Location
    Stockholm
    MS-Off Ver
    2016
    Posts
    13

    Re: Summing values based on increasing date + additional criteria

    Quote Originally Posted by t.gatheru View Post
    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.

  6. #6
    Registered User
    Join Date
    06-11-2020
    Location
    Stockholm
    MS-Off Ver
    2016
    Posts
    13

    Re: Summing values based on increasing date + additional criteria

    Quote Originally Posted by CARACALLA View Post
    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!

  7. #7
    Registered User
    Join Date
    06-11-2020
    Location
    Stockholm
    MS-Off Ver
    2016
    Posts
    13

    Re: Summing values based on increasing date + additional criteria

    Quote Originally Posted by t.gatheru View Post
    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.

  8. #8
    Registered User
    Join Date
    02-05-2020
    Location
    Nairobi, Kenya
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Summing values based on increasing date + additional criteria

    Quote Originally Posted by Louise Alice View Post
    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

  9. #9
    Registered User
    Join Date
    06-11-2020
    Location
    Stockholm
    MS-Off Ver
    2016
    Posts
    13

    Re: Summing values based on increasing date + additional criteria

    Quote Originally Posted by t.gatheru View Post
    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?

  10. #10
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    831

    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

  11. #11
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    831

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

  12. #12
    Registered User
    Join Date
    06-11-2020
    Location
    Stockholm
    MS-Off Ver
    2016
    Posts
    13

    Re: Summing values based on increasing date + additional criteria

    Quote Originally Posted by CARACALLA View Post
    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

  13. #13
    Registered User
    Join Date
    06-11-2020
    Location
    Stockholm
    MS-Off Ver
    2016
    Posts
    13

    Re: Summing values based on increasing date + additional criteria

    Quote Originally Posted by CARACALLA View Post
    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!

  14. #14
    Registered User
    Join Date
    06-11-2020
    Location
    Stockholm
    MS-Off Ver
    2016
    Posts
    13

    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?

  15. #15
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    831

    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

  16. #16
    Registered User
    Join Date
    06-11-2020
    Location
    Stockholm
    MS-Off Ver
    2016
    Posts
    13

    Re: Summing values based on increasing date + additional criteria

    Quote Originally Posted by CARACALLA View Post
    =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...

  17. #17
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    831

    Re: Summing values based on increasing date + additional criteria

    order number works

    Attach a file to try it out

  18. #18
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    831

    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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 11-15-2016, 03:47 AM
  2. Default Summing values based on criteria
    By Corsa88 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-04-2015, 11:45 PM
  3. [SOLVED] Sum of values based on values in different column with additional criteria
    By jimmyb555 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2015, 02:31 PM
  4. [SOLVED] Summing Largest 50 values based on criteria
    By Scooby5 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-27-2014, 10:38 PM
  5. Summing based on date criteria
    By R0bert0 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-21-2007, 01:26 PM
  6. Summing values based on two criteria
    By smartguy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-16-2007, 04:46 PM
  7. Summing Values Based on Text Criteria
    By ExcelTip in forum Tips and Tutorials
    Replies: 4
    Last Post: 11-02-2005, 10:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1