+ Reply to Thread
Results 1 to 7 of 7

How to combine repeated data to give a total value

Hybrid View

  1. #1
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    81

    How to combine repeated data to give a total value

    I've a speadsheet of product sales data.
    The sheet shows sales by product code and the quantity/value per order reecieved.

    Is there a way for me to add all of the values per product code to give a total for that piece?

    See the attachment for an example of what I want to do. I want to somehow run a formula that will automatically collate the values per product code.
    Eg.
    Item no. 53040 is repeated 13 time - I'd want to just show this item once with the combined quantity of 69 and value of 222.18

    I have a lot of data so need to find a way to do this quickly and easily if possible?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,076

    Re: How to combine repeated data to give a total value

    You could do this

    in E2
    =SUMIF(A$2:AA$40,A2,C$2:C$40)
    in F2
    =SUMIF(A$2:AA$40,A2,D$2:D$40)
    and copy down the columns

    This will repeat the values which you may not want, otherwise a Pivot Table would do this but require more work.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    81

    Re: How to combine repeated data to give a total value

    What would the new formula be if I did it that way John?

  4. #4
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    81

    Re: How to combine repeated data to give a total value

    I Think that wil do the job thanks. I can then just copy and paste values then remove duplicates from column A?

  5. #5
    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
    30,684

    Re: How to combine repeated data to give a total value

    Simply copy & paste column A to another column, "Remove "Duplicates" and amend "Special_k" 's formulae to reference the new column.

  6. #6
    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
    30,684

    Re: How to combine repeated data to give a total value

    =SUMIF($A:$A,$H2,C:C)

    and

    =SUMIF($A:$A,$H2,D:D)

    where column H had your unique list

  7. #7
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    81

    Re: How to combine repeated data to give a total value

    Thanks to John and SpecialK - problem now solved.

+ 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] Subtract dates to give a total amount of days - Multiple Data
    By t_man000 in forum Excel General
    Replies: 2
    Last Post: 07-23-2015, 11:14 PM
  2. [SOLVED] Help on formula to calc total sum of partused by repeated serial no for different pudo val
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-01-2014, 11:42 PM
  3. Excel 2007 : Combine data into single row with total
    By gypsi96 in forum Excel General
    Replies: 5
    Last Post: 07-19-2011, 11:38 AM
  4. Combine and Total sales data into new sheet
    By kelemvor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2011, 03:16 PM
  5. [SOLVED] Append data to repeated values with a fixed array to be repeated on value change
    By anchuri_chaitanya in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-06-2011, 12:30 AM
  6. Count repeated vaule and total sum
    By dpatel in forum Excel General
    Replies: 5
    Last Post: 08-05-2010, 09:35 AM
  7. Replies: 5
    Last Post: 08-06-2009, 05:17 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