+ Reply to Thread
Results 1 to 17 of 17

SUM for two different list

  1. #1
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    121

    SUM for two different list

    Hi Everyone,

    I have ID and Value columns in two different tab. Do you know how I can sum them into a third tab in same column names ?

    IDs are same, just in not same order. I tried SUMIF, SUMIFS formulas but couldnt manage

    I would be so happy if you show something !

    Sample is attached.

    Thanks a lot
    Orhan
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: SUM for two different list

    There are no Employee ID's in the 'SUM' worksheet.

    If there were, which I expect is your intent, you can use this in B2:

    =SUMIF(Data1!A:A,A2,Data1!B:B)+SUMIF(Data2!A:A,A2,Data2!B:B)

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: SUM for two different list

    In your example file you have exactly the same entries in column A of sheets Data1 and Data2. Is that representative of your real data, or might you have some items in one sheet that does not occur in the other sheet? Are you likely to have duplicates in either list?

    Pete

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: SUM for two different list

    In "SUM" sheet "B2"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Define sheet name as sheets
    $A$2:$A$9 is criteria range
    A2 is criteria
    $B$2:$B$9 is sum range.
    Refer attach file.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: SUM for two different list

    maybe try PowerQuery
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: SUM for two different list

    Array formula in A2 then drag down
    Please Login or Register  to view this content.
    Formula in B2 then drag down
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    121

    Re: SUM for two different list

    Quote Originally Posted by sandy666 View Post
    maybe try PowerQuery
    Thank you sandy ! Actually, PowerQuery would be the best solution for me since all of my example files are actually a Powerquery file. I didnt know its possible with PQ thats why I didnt mention. I just started to power query, may I kindly ask another question for the power query ?

    My actual case which I am trying to solve is :

    I have a tracker with employee IDS and 12 column for each month where I am putting there each month usage of discount codes. Like 10 in january and 20 in february. Secondly, I am receiving a monthly file again with IDS from our vendor where they are saying cumulated how much discount code usage until that month. E.g 40 in March. So, I am making a calculation which is 10+20= 30 in Jan and Feb. so 40-30=10 is March. So I am adding that result "10" to the March column in my tracker. So I want to use power query to access that monthly file which I receive from vendor to calculate automatically and populate the related month in current month.

    IS it also possible this activity with Power Query ?

    I am attaching my power query file as for your info.

    Thanks a million !
    Attached Files Attached Files
    Last edited by orhanceliloglu; 04-25-2018 at 04:43 AM.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUM for two different list

    Is this external tracker: DataNet Tracker_Orhan.xlsm ?
    Files which you receive are always with the same names ?
    Data in these files has always the same type and structure ?
    Could you attach example of external tracker (desensitized) and show what you really want to achieve (do it manually if necessary) ?

    paths, structure and type of data has meanings in PQ

    edit:
    I forgot to answer: probably it is possible
    Last edited by sandy666; 04-25-2018 at 09:22 AM.

  9. #9
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    121

    Re: SUM for two different list

    Quote Originally Posted by sandy666 View Post
    Is this external tracker: DataNet Tracker_Orhan.xlsm ?
    Files which you receive are always with the same names ?
    Data in these files has always the same type and structure ?
    Could you attach example of external tracker (desensitized) and show what you really want to achieve (do it manually if necessary) ?

    paths, structure and type of data has meanings in PQ

    edit:
    I forgot to answer: probably it is possible
    Thank you very much again !

    Actually I have put the file monthly file that I receive as Data2 tab but let me upload separately.

    Mail file has "Fixed_tracker" tab where I need to input data from monthly file that I received. And yes that file I am receiving always same name, same structure. I attached it as "Monthly file"
    I also attached "Achievement" file to show what I really want to achieve.

    So in detail what I want to achieve : I have in my fixed tracker tab 12 column for each month. in Main file before achievement I have only data for Jan and Feb. You will see in "achivement" file, March column in populated. That's what I want to achieve. For example I have in January as value 10 and February value is 20 in my fixed tracker. And then I am receiving the monthly file with an amount of cumulated. Monthly file has a column as "USED AMOUNT" and lets say its 70. And with power query I want to pull that data, and minus from my tracker Sum which was 30 (sum of Jan and Feb) In this way I will understand this employee have 40 in March. (70 - (20+10) = 40) And I want to populate that 40 into the March column in fixed_tracker tab.

    I hope its clear
    Attached Files Attached Files

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUM for two different list

    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content] - doesn't exist
    could you do the same with a CLEAN files? With BEFORE and AFTER

    basically you want "import" data from monthly file into appropriate columns into main file?
    Last edited by sandy666; 04-25-2018 at 11:34 AM.

  11. #11
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    121

    Re: SUM for two different list

    Quote Originally Posted by sandy666 View Post
    Fixed Tracker Power Query Table 3 - doesn't exist
    could you do the same with a CLEAN files? With BEFORE and AFTER

    basically you want "import" data from monthly file into appropriate columns into main file?

    I am sorry, here I cleaned the Table3 errors. It was only in Achievement file. Rest is same. But I didnt understand what do you mean BEFORE and AFTER ?

    Yes, I want "import" data from monthly file into appropriate columns into main file to "Fixed tracker" tab.

    Let me know if you need anything else.

    Many many thanks again for your effort on this.

    Orhan
    Attached Files Attached Files
    Last edited by orhanceliloglu; 04-25-2018 at 11:31 AM.

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUM for two different list

    Main file

    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], - doesn't exist

    Expression.Error: We couldn't find an Excel table named 'Table3'.
    Details:
    Table3

  13. #13
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    121

    Re: SUM for two different list

    Quote Originally Posted by sandy666 View Post
    Main file

    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], - doesn't exist

    Expression.Error: We couldn't find an Excel table named 'Table3'.
    Details:
    Table3
    Sorry Table3 is an old name. All table names should be Table1 instead 3

    Re uploaded the Main excel file.
    Attached Files Attached Files

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUM for two different list

    That is why I said CLEAN

    Ok, I'll look at this

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUM for two different list

    How Excel should recognize month in monthly file? There is nothing to do with.
    I can join FixedTracker with MonthlyTable but how do I know file is from Jan or Mar ?

    edit:
    the wall is if I want take data from new monthly file it will replace old data automatically after refresh.
    problem with date (month) - I can define DateTime.LocalNow() but I don't have the point where two months from two files meet. (CREATION_DATE isn't that what I want)
    probably you'll need vba to copy new data and calculate what you want but I am far far away from vba so you can try on vba sub-forum.

    sorry
    Last edited by sandy666; 04-25-2018 at 06:09 PM.

  16. #16
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    121

    Re: SUM for two different list

    Quote Originally Posted by sandy666 View Post
    How Excel should recognize month in monthly file? There is nothing to do with.
    I can join FixedTracker with MonthlyTable but how do I know file is from Jan or Mar ?

    edit:
    the wall is if I want take data from new monthly file it will replace old data automatically after refresh.
    problem with date (month) - I can define DateTime.LocalNow() but I don't have the point where two months from two files meet. (CREATION_DATE isn't that what I want)
    probably you'll need vba to copy new data and calculate what you want but I am far far away from vba so you can try on vba sub-forum.

    sorry
    Thank you anyway sandy. I will check VBA solution as you advised.

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUM for two different list

    You are welcome

    Probably with VB will be faster and easier but like I said I am far away from that

    have a nice day

+ 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: 2
    Last Post: 02-22-2015, 11:40 AM
  2. Replies: 3
    Last Post: 04-22-2014, 03:30 PM
  3. Replies: 1
    Last Post: 11-05-2013, 12:40 AM
  4. Replies: 0
    Last Post: 01-09-2013, 11:44 AM
  5. [SOLVED] Taking a list of tasks and a list of subtasks and creating a new list with groupings
    By kknb0800 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-06-2012, 12:16 PM
  6. Replies: 3
    Last Post: 07-20-2012, 09:52 AM
  7. list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
    By Max in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 08:05 AM

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