please help to calculate how often a number appears and calculate total value

hi everyone

i need some help and im not sure how to word it so apologies if i dont make much sence

i have data from a stock take and it totals 798 row. using this data i need to see how offten the unique code appears in the data and then gather the total quantity from the next column. eg below

uniqe code -------------- quantity
4017267948919 ............. .10
4017267948777................7
4017267942222...... .....76
4017267948777................54
4017267948919 ...............55
4017267942222............3

would it be possible to get the result on a new sheet in the format of

unique code ................ . total
4017267942222.............79


once again thanks alot and any help would be greatly appricated


mukhtar

Use the =countif function

look at this tutorial

https://www.techonthenet.com/excel/formulas/countif.php

Hi mukhtar1211 - The attached workbook demonstrates how to list and summarize your data.

Data worksheet holds 30 rows of random codes and quantities.
Summary worksheet:

Column A= a complete list of unique codes. This ARRAY FORMULA in A2:
=IFERROR(INDEX(Data!$A$2:$A$31,MATCH(0,COUNTIF($A$1:$A1,Data!$A$2:$A$31),0)),"")

Column B= total quantity associated w each code. This regular formula in B2:
=IF(A2="","",SUMIF(Data!$A$2:$A$31,Summary!A2,Data!$B$2:$B$31))

Hope this meets your requirements. - Lee

NOTE: When pasting or editing an ARRAY FORMULA in the formula bar, you must press CTRL+SHIFT+ENTER to confirm entry.
Then use the drag handle to copy the cell down or across.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Originally Posted by mukhtar1211

hi alan

thanks for the super fast responce

please may you help me a little more

im trying figure it out and i can get it to tell me how many times the code appears but how would i then tell it to added to quanity in the next coloumn and give me a total.

thanks

If you have already managed to get COUNTIF to work, the next step would be to use SUMIF() to run the totals, based on the exact same criteria as the COUNTIF