+ Reply to Thread
Results 1 to 5 of 5

Extract and sum data from 5 columns with different products and their counts per product?

  1. #1
    Registered User
    Join Date
    11-08-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    10

    Extract and sum data from 5 columns with different products and their counts per product?

    Hello I manage inventory of beverages for 5 clubs, everyday I sell different products in every club, everyday I insert with barcode scanner UPS product codes and on the right numbers of beverages, how can I get the table with unique barcodes and sum of their counts?
    inventory-nightclubs-test.xlsx

    A B C D E F G H I J K L
    CLUB(1) COUNT(1) CLUB(2) COUNT(2) CLUB(3) COUNT(3) (4) COUNT(4) (5) COUNT(5) BARCODES(ALL) COUNT(ALL)
    444 2 333 1 444 1 222 5 111 23 111 69
    333 3 222 4 111 4 888 34 222 11 222 27
    222 3 111 5 333 4 333 12 333 23
    555 3 777 4 555 4 999 34 444 11
    111 3 555 4 222 4 111 34 777 11
    777 3 444 4 888 4 444 34 888 11

    There's always in every CLUB's column only one time 444 - 111 - 333 etc value because for example 444 will represent a whiskey 70cl.
    and presence of value 444 and count 2 will say that I have sold 2 bottles of whiskey 70cl in CLUB 1 that day

    so....
    111
    222
    333 represents barcodes (it also can be a text)

    COUNT is number of beverages sold that day

    What I want is to get this statistics:
    product (111) has been sold 3+5+4+34+23 = 69 that day
    222 should be 3+4+4+5+11 etc.

    Can anybody tell me the function to get all unique beverages=barcodes and their counts in column K and L and cells K3 and L3 and below?
    Last edited by danfolt; 02-14-2013 at 05:33 PM. Reason: adding file in attach

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,365

    Re: Extract and sum data from 5 columns with different products and their counts per produ

    Hi,

    The simplest, but admittedly not the most elegant is
    L3:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    There's no doubt also an array formula out there.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-08-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Extract and sum data from 5 columns with different products and their counts per produ

    Thank you very much Richard for your very quick answer, will try it tonight, I will need to modify the formula because the excel file which keeps barcode data I have added now into the first post has a little bit more columns.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,365

    Re: Extract and sum data from 5 columns with different products and their counts per produ

    Hi,

    I also notice that I'd forgotten to make the ranges absolute, so in order to be able to copy down

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-08-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    10

    Extract and sum data from 5 columns with different products and their counts per product

    Richard, I have modifed the formula to fit the file which has more columns which I use to specify unique products and counts and column K= column AT (in my file) gives me always 0 , I have tried to only copy and paste and also CTRL+SHIFT and ENTER but always 0 , can you pls. take a look at the file here ?
    Attached Files Attached Files

+ 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