Formula To ADD values from different columns

Closed
testinglaurier Posts 2 Registration date Thursday January 2, 2014 Status Member Last seen January 8, 2014 - Jan 2, 2014 at 04:51 PM
testinglaurier Posts 2 Registration date Thursday January 2, 2014 Status Member Last seen January 8, 2014 - Jan 8, 2014 at 09:14 AM
Hi I am new to excel and need help in adding values in my sheet.

the contents are as follows:


NAME -------- TAG ---- CODE ---- QTY ---- BIN#
AIRWEAR ---- A411 ---- 0ES3 ---- 1 ---- 1
ARISTAR ---- 6344 ----017 ---- 1 ---- 1
AIRWEAR ---- A405 ---- 0DJ3 ---- 1 ---- 1
AIRWEAR ---- A402 ---- 0E98 ---- 1 ---- 2
AIRWEAR ---- A416 ---- 0TM7 ---- 1 ---- 2
AIRWEAR ---- A405 ---- 0DJ3 ---- 1 ---- 2
AIRWEAR ---- A411 ---- 0ES3 ---- 1 ---- 2


The above are some values in my sheet, now I want to add the qty of the items based on the bin number, so basically i need to know how many items are there in each bin, is there a formula i can use to find out, i have around 2300 rows of items with approx 300-400 bins and i dont want to manually count each item and bin. Sorry if my explanation is confusing. Thank you for your help.

regards,
Laurier

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 3, 2014 at 11:38 PM
the data is SORTED ACCORDING TO BIN

then in F2 type this formula

=IF(COUNTIF(E2:$E$8,E2)>1,"",SUMPRODUCT(($E$2:$E$8=E2)*($D$2:$D$8)))
copy F2 down

result will be like this

NAME TAG CODE QTY BIN#
AIRWEAR A411 0ES3 1 1
ARISTAR 6344 17 1 1
AIRWEAR A405 0DJ3 1 1 3
AIRWEAR A402 0.00E+00 1 2
AIRWEAR A416 0TM7 1 2
AIRWEAR A405 0DJ3 1 2
AIRWEAR A411 0ES3 1 2 4
0
testinglaurier Posts 2 Registration date Thursday January 2, 2014 Status Member Last seen January 8, 2014
Jan 8, 2014 at 09:14 AM
Thank you so much Venkat! I greatly appreciate it.
0