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

1. ## 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?

2. ## 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:
`Please Login or Register  to view this content.`

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

3. ## 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. ## 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:
`Please Login or Register  to view this content.`

5. ## 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 ?

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

#### 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