+ Reply to Thread
Results 1 to 16 of 16

Sum values based on variable criteria [dynamic arrays]

  1. #1
    Registered User
    Join Date
    07-28-2021
    Location
    Lithuania
    MS-Off Ver
    MS 365
    Posts
    6

    Sum values based on variable criteria [dynamic arrays]

    Hello,

    I'm struggling with finding solution for my problem.

    In file attached there are 3 tables. In TABLE A I would like to return sum based on my criteria where sum range and criteria range would contain formulas A and B (shown in TABLE B). I'm not sure whether these formulas can work in similar manner but that's how I'm trying to explain what they should do.

    Formula A should calculate amount depending on Code 2 (either Amount_end picked up or amount_end - amount_start). Then Formula C would look in separate TABLE C and return Code 1 equivalent for Code 2 as in TABLE A my array is with Code 1.

    It would be easy if I actually executed formulas A and B in TABLE 2 and then used returned values in formula C, but I cannot do that as I have tables in separate sheets and table 2/3 cannot be modified with additional calculations (or it would be inefficient as there are too many of them)

    I would appreciate any help which would guide me to write proper formula or perhaps this is possible only with VBA code?

    Thanks
    scrin

    P.S. attached both image and excel file
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by scrin; 08-02-2021 at 02:21 PM. Reason: updated thread

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: SUMIFS with formulas in sum/criteria range

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-28-2021
    Location
    Lithuania
    MS-Off Ver
    MS 365
    Posts
    6

    Re: SUMIFS with formulas in sum/criteria range

    I have updated thread with excel file for your convenience however there is no more data than shown in image

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: SUMIFS with formulas in sum/criteria range

    Thanks. The point is that I am not going to waste time recreating your data in order to help you when you can provide it yourself.

    I will now have a look at your issue.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: SUMIFS with formulas in sum/criteria range

    Try this:

    =LET(a,VLOOKUP(B4,$B$28:$C$43,2,0),VLOOKUP(a,$B$16:$C$25,2,0)&" - "&VLOOKUP(a,$B$16:$D$25,3,0))

    You will need to change commas to semi-colons for your locale, so please see the workbook attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-28-2021
    Location
    Lithuania
    MS-Off Ver
    MS 365
    Posts
    6

    Re: SUMIFS with formulas in sum/criteria range

    That's fabulous formula, I didn't know it existed I adapted it a bit to my actual problem and it worked.

    Thank you for your help!

  7. #7
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Assign variables inside formula - LET formula

    I took a detailed look at Title is Let formula and I only saw AliGW when I replied. It was correct and I misunderstood.

    Cell C4 formula , Drag down

    HTML Code: 
    Last edited by wk9128; 08-02-2021 at 06:56 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Assign variables inside formula - LET formula

    You may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  9. #9
    Registered User
    Join Date
    07-28-2021
    Location
    Lithuania
    MS-Off Ver
    MS 365
    Posts
    6

    Re: SUMIFS with formulas in sum/criteria range + LET function

    In relation to my issue above I have came across other issue. My initial intention was to sum values from table 2 based on criteria from table 1 / 3. vlookup function works fine until all values from table 1 have 1 unique matching value from table 3 but there are more than 1 matching value in table 3 and in such case I would prefer using sumif function where criteria would be variable array.

    I noticed that sum(sumif()) function would work with array as criteria but it gives error once I try to create this variable array in LET() formula. See excel attached. I wrote formula in cell G22 how I imagine it should work (in calculation part I would put formula from step 3). My expectation of result is in green-colored field. I manually created one-off array what should be the output.

    So there are 2 problems:
    1) Formula in cell G18 doesn't return full list of values. I need to drag down formula to return the rest of values (i.e. values in cells G19 and G20). How to return full list with one formula in one cell?
    2) Formula in cell G22 doesn't fit in LET function, how could I upgrade it?
    Attached Files Attached Files
    Last edited by scrin; 08-02-2021 at 10:23 AM. Reason: updated excel file

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: SUMIFS with formulas in sum/criteria range

    This is quite a different proposition to your original query - I would say you've shifted the goalposts not just a bit, but onto an entirely different pitch!!!

  11. #11
    Registered User
    Join Date
    07-28-2021
    Location
    Lithuania
    MS-Off Ver
    MS 365
    Posts
    6

    Re: SUMIFS with formulas in sum/criteria range

    Sorry if those look different however my original problem was summing up numbers from TABLE 2 (either directly "Amount_end" or substraction result between "Amount_end" and "Amount_start"). Your answers gave me very nice clue where to start from and I'm very thankful for that. I'm thinking I still left with the topic about how to sum values when I have variable in formula?

    If necessary I will close topic in this thread and will create separate one in new thread.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: SUMIFS with formulas in sum/criteria range

    I don't think you need a new thread, however you might want to update the title of the opening post. Do NOT edit any of the text in your earlier posts, though - leave them exactly as they are now.

  13. #13
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Sum values based on variable criteria [dynamic arrays]

    Would you pls try other methods to solve this problem

    Please change all text and digital forms to pure digital forms, the formulas will be easier to handle

    Cell C4 formula , drag down
    HTML Code: 

    If you keep the status file, the formula will be a little more troublesome
    Cell C4 Array formula , drag down
    HTML Code: 

  14. #14
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,775

    Re: Sum values based on variable criteria [dynamic arrays]

    C4=TRANSPOSE(FILTER($C$28:$C$35,$B$28:$B$35=B4,""))

    Copy down



    F4=SUMPRODUCT(SUMIF($B$16:$B$23,C4#,$D$16:$D$23))

    Copy down

  15. #15
    Registered User
    Join Date
    07-28-2021
    Location
    Lithuania
    MS-Off Ver
    MS 365
    Posts
    6

    Re: Sum values based on variable criteria [dynamic arrays]

    I have already applied wk9128's method. It's in fact more simple than my way, thank you!

  16. #16
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Sum values based on variable criteria [dynamic arrays]

    You're Welcome. @scrin Thank You for the feedback

+ 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: 1
    Last Post: 04-09-2021, 11:09 AM
  2. [SOLVED] Usine sumifs to change criteria range column based on dynamic criteria
    By Luiscarlos in forum Excel General
    Replies: 5
    Last Post: 11-19-2020, 09:33 AM
  3. Replies: 5
    Last Post: 11-22-2019, 07:12 AM
  4. Replies: 3
    Last Post: 02-02-2017, 04:32 AM
  5. Replies: 5
    Last Post: 10-03-2016, 03:39 AM
  6. Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 PM

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