+ Reply to Thread
Results 1 to 6 of 6

Sum without repetition

  1. #1
    Registered User
    Join Date
    05-01-2012
    Location
    Doha, Qatar
    MS-Off Ver
    Excel 2007
    Posts
    13

    Sum without repetition

    I have an excel file with two sheets. The first sheet contains raw data received from barcode readers at my factory workshop. The second sheet is where I want to prepare a report using the data in the first sheet.

    Each product is scanned at every station it passes through. So in the raw data, each product will be mentioned multiple times depending on how many stations it had to go to. What i want to do is calculate the total area produced, but in this calculation I cannot just use "sum", because then I will be adding the area of the products maybe twice or three times depending on how many times the product was repeated in the data. So I want to add the area of each product without repeating it again.

    I know it sounds simple and confusing at the same time, thats why I have add a sample to make my case clear.

    Awaiting your kind assistance.
    Attached Files Attached Files
    Last edited by ratkiller; 02-16-2013 at 06:26 AM. Reason: forum rules

  2. #2
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    179

    Re: Advanced formulas and functions

    Hi ratkiller,

    Have you considered doing a sumif divided by a countif? This would give you the average values of each product, but since the values are the same each time, you would just get the exact values.
    Did I help? Click *- add to my rep.

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Advanced formulas and functions

    ratkiller,

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Registered User
    Join Date
    05-01-2012
    Location
    Doha, Qatar
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Advanced formulas and functions

    I tried your suggestion, but I wasn't able to get it right. Maybe you can help me with the syntax? give me an example

  5. #5
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: Sum without repetition

    So for "Square to round" should it be divided by the to unique values =SUM(0.706+0.434) + all other constraints.
    //Ola
    Last edited by olasa; 02-16-2013 at 07:49 AM.

  6. #6
    Registered User
    Join Date
    05-01-2012
    Location
    Doha, Qatar
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Sum without repetition

    I got it, first I added a new column and made a specific reference for each item. then I used the following formula

    =(SUM(1/COUNTIF($V$2:$V$10,$V$2:$V$10)))*M10

    this gave me the total area of each item without repeating it.

    If anyone wants the excel file to see how it works, please msg me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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