+ Reply to Thread
Results 1 to 9 of 9

SUM of clumps of different items sold

Hybrid View

  1. #1
    Registered User
    Join Date
    12-02-2008
    Location
    fort wayne
    Posts
    17

    SUM of clumps of different items sold

    Guys and Gals,
    Please be so kind and help with a formula for the following dillemma:

    I have a 7000 row spreadsheet of items sold over the past 6 months. I sorted them by their SKU, but some of them sold in multiple qty in a given day.

    What is a formula to calculate the total qty of each SKU sold?

    Example:
    Column A = SKU 001
    Column B = Qty Sold in a particular purchase (most have 1, but some are 2 or 3 or 4, etc.)
    Column C, (Row 10) = Total where I would like to come up with a number for columns 1-10 (for example).

    next item:
    Column A = SKU 002
    Column B = Qty Sold in a particular purchase (most have 1, but some are 2 or 3 or 4, etc.)
    Column C, (Row 17) = Total where I would like to come up with a number for columns 11-17 (for example).

    P.S. Rows per SKU vary (they represent each purchase of a given item)

    THANK YOU INFINITELY IN ADVANCE FOR A SOLUTION
    Last edited by 47magic; 06-12-2009 at 08:58 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: SUM of clumps of different items sold

    Can you upload an example of that spreadsheet? Are all 3 columns (A,B,C) in all rows? Do you have multiple listings of SKU numbers?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    12-02-2008
    Location
    fort wayne
    Posts
    17

    Re: SUM of clumps of different items sold

    I would love to! How do you upload a file?

    I tried copy/paste a section, but it came out scrambled.

  4. #4
    Registered User
    Join Date
    12-02-2008
    Location
    fort wayne
    Posts
    17

    Re: SUM of clumps of different items sold

    Here is a snapshot of a portion of the document.
    Attached Images Attached Images

  5. #5
    Registered User
    Join Date
    12-02-2008
    Location
    fort wayne
    Posts
    17

    Re: SUM of clumps of different items sold

    As you see in the snapshot, I manually calculated (=sum(AC2-AC21) for example) total qtys sold for first 4 items.

    Problem is that there are 7066 rows and it would take forever to do this method. Therefore I was looking for help with a formula I can plug in the column AD and just drag it down.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: SUM of clumps of different items sold

    Lets' say your SKU start in C2 and your QTY in D2, In E2 try
    =IF(B1<>B2,SUMPRODUCT(--($C$2:$C$8000=C2),($D$2:$D$8000)),"")
    Dragged down. Does that work for you?

  7. #7
    Registered User
    Join Date
    12-02-2008
    Location
    fort wayne
    Posts
    17

    Re: SUM of clumps of different items sold

    ChemistB, thanks for all your help.
    I am sorry for my confusion in this. What do letters B stand for in your formula?

    To be exact here are my actual columns:

    AB2 through AB7066 = SKU
    AC2 through AC7066 = QTY of each SKU sold per order
    AD2 through AD7066 = Total QTY of each SKU sold

    Can you please plug these Column Names into the formula for me?

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: SUM of clumps of different items sold

    Okay, this assumes you have sorted your SKU #'s. In AD2 and dragged down
    =IF(AB2<>AB3,SUMPRODUCT(--($AB$2:$AB$8000=AB2),($AC$2:$AC$8000)),"")
    Does that work?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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