+ Reply to Thread
Results 1 to 28 of 28

Same Name With Different Values and Sum of them

  1. #1
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Same Name With Different Values and Sum of them

    Good evening,

    I'm facing a problem making my excel. I have a certain set of data having the companies names in column B2:B101 ( Lot of entries with same companies name but different values in Column D ). and the values in D2:D101, inclusive of negative values also.

    is it possible to find out the company name with all the values present in column D in front of all the same name.

    For Eg. If the company ABC has 6 entries all over the column B but with different values. So is it possible to list the name ABC once and with SUM of all the six entries in front of ABC ?

    If yes then please guide how do I do it..

    I hope I am not confusing you all by my question.

    Thanks & Regards

    Avinash

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Same Name With Different Values and Sum of them

    Sounds like you need to use the SUMIFS function https://exceljet.net/functions/sumifs-function

  3. #3
    Registered User
    Join Date
    02-20-2019
    Location
    Riyadh
    MS-Off Ver
    Microsoft Office 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    =SUMPRODUCT((B2:B101=G2)*D2:D101)

    G2=Company ABC

    i hope this will work for you if you like add reputation*
    Last edited by ibrmoh; 09-05-2023 at 11:26 AM.

  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,369

    Re: Same Name With Different Values and Sum of them

    Post formulae to the thread, please, not pictures. Thanks.
    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.

  5. #5
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Quote Originally Posted by ibrmoh View Post
    =SUMPRODUCT((B2:B101=G2)*D2:D101)

    G2=Company ABC

    i hope this will work for you if you like add reputation*
    Dear ibrmoh,

    Thank You so much for your prompt reply.

    I have 100 rows and 37 different companies with duplicate names but different values.
    What I want is to have only 37 company names with the sum of all the repeated company values instead of repeated 100 names..

    for eg. if I have the company abc 6 rows, company jkl 7 rows, company efg 5 rows and so on.. I need to have only one entry for all the repeated companies.. i.e. for all the 6 entries of ABC there must be ONLY 1 entry ABC and sum of all the 6 rows value..

    eg.

    ABC : 5
    ABC : 6
    ABC : -4
    ABC : -7
    ABC : 9
    ABC : -2

    So the result must be like : ABC : 7 ( Sum of all the ABC Values )

    Thanks & Regards
    Avinash

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Same Name With Different Values and Sum of them

    Did you look at the sumifs function I suggested?

  7. #7
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Quote Originally Posted by Fluff13 View Post
    Did you look at the sumifs function I suggested?
    Dear Fluff13,

    Thank you so much for your reply.

    I did tried your suggestion but it does not helping me to restrict the duplicate entry in names. it does add the other duplicate entry values but simultaneously it adds some numbers for every other duplicate entry which is quite misleading and not required.

    Regards
    Avinash

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Same Name With Different Values and Sum of them

    In that case can you provide a workbook showing what you have & what you want.

  9. #9
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Dear Fluff13,

    I'm sending an attachment of a Sample Work Book

    Please have a look at it

    Regards

    Avinash
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-20-2019
    Location
    Riyadh
    MS-Off Ver
    Microsoft Office 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Hello Avinash,

    No matter how many companies you have, can use countif/sumif function also. If you can share your Excel sheet will be more helpful in solving the issue.

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Same Name With Different Values and Sum of them

    You can use
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Dear Fluff13,

    its working perfect, fantastically fine,
    only issue is, I'll have to write all the 37 or 47 or 77 company's names, only then it shows the sum of values
    any other way out to automate it ? I mean, if I load the raw data as per in sample data, the result should be there without writing manually all the company's names

    Regards

  13. #13
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Same Name With Different Values and Sum of them

    In H4 dragged down use
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Same Name With Different Values and Sum of them

    An alternative solution with Power Query
    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  15. #15
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Dear Alansidman,

    Thank you for your kind reply.. but I'm using an older version so power query is not an option for me

    Regrads

  16. #16
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Dear Fluff13,

    I tried your formula but it throws only blank cell. Nothing is happening

    Regards

  17. #17
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Quote Originally Posted by Fluff13 View Post
    In H4 dragged down use
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dear Fluff13,

    I tried your formula but it throws only blank cell. Nothing is happening

    Regards

  18. #18
    Registered User
    Join Date
    02-20-2019
    Location
    Riyadh
    MS-Off Ver
    Microsoft Office 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Sample.xlsx

    check this out, hope this works for you only thing is you have only one date.

    add reputation if you like

  19. #19
    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,369

    Re: Same Name With Different Values and Sum of them

    @ ibrmoh

    For the second time in this thread ...

    Administrative Note:

    Please include formulae used in your post in addition to providing a workbook (for the benefit of members unable to download attachments).

    Thanks.

  20. #20
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Quote Originally Posted by ibrmoh View Post
    Attachment 841829

    check this out, hope this works for you only thing is you have only one date.

    add reputation if you like

    Dear ibrmoh

    Wow.. its working like charm.. Abslotely fantastic..

    Thank You So Much for this great help



    Thanks & Regrads

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Same Name With Different Values and Sum of them

    Fluff13 formula works fine:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  22. #22
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Dear All,

    I want to Thank all the members who have spared their precious time & replied, answered my queries and helped me solving it.
    Once again Thanks to all of you

    Regards

    Avinash

  23. #23
    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,369

    Re: Same Name With Different Values and Sum of them

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) 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 each of those who offered help.

  24. #24
    Registered User
    Join Date
    01-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Same Name With Different Values and Sum of them

    Dear all,

    Special Thanks To the members ibrmoh & Fluff13.

    I have added reputation & marked the thread as " SOLVED " as well

    Regards

    Avinash

  25. #25
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Same Name With Different Values and Sum of them

    Glad to help & thanks for the feedback.

  26. #26
    Registered User
    Join Date
    02-20-2019
    Location
    Riyadh
    MS-Off Ver
    Microsoft Office 2010
    Posts
    18

    Thumbs up Re: Same Name With Different Values and Sum of them

    Dear All,

    Glad to help and join new members and learn. This is a very good forum, I love it.

    Sorry! AliGW for breaking the rules I shall follow the forum rules.

    Thanks to Fluff13 and Avinash for the good feedback.

  27. #27
    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,369

    Re: Same Name With Different Values and Sum of them

    Sorry! AliGW for breaking the rules I shall follow the forum rules.
    You didn't break any, but I had asked you previously to include formulae in your posts, and you went and failed to do so again, so I made my request a bit 'louder' so that you'd take notice.

  28. #28
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Same Name With Different Values and Sum of them

    Power Query is available for you. It is an add in for XL2010

    https://blog.enterprisedna.co/how-to...uery-to-excel/

+ 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. [SOLVED] VBA Code replace old values to new values depends upon column values
    By johnlara in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2015, 08:19 AM
  2. [SOLVED] VBA code to look up a list of values based on entered values and return all values.
    By dnwadams in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2015, 10:14 PM
  3. [SOLVED] Extract values (row values and column values) with formula
    By bjnockle in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-17-2014, 10:13 AM
  4. [SOLVED] vba to Replace Old values by new values depends upon cell values in AC:AC col
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-25-2014, 08:15 AM
  5. Replies: 0
    Last Post: 10-12-2012, 01:08 PM
  6. [SOLVED] How to lookup values same row values different column values
    By kgonzalbo in forum Excel General
    Replies: 5
    Last Post: 05-22-2011, 01:49 AM
  7. assigning date entries to week values and month values to sum column C-N values C-
    By the accountant in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2010, 09:52 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