EXCEL SUMIFS formula of two columns removing dupliate entries

Jase Alexander
Jase Alexander used Ask the Experts™
on
HI Guys

Hope you can help

Ive been trying to use SUMIF, SUM PRODUCT with FREQUENCY and MATCH to sum the quantities in stock locations but ignoring duplicate entries

I have column A and Column B - column A has the stock location which can be repeated several times with the unique stock quantity against the location in Column B

Ive tried creating a formula to sum the quantities in Column B but only for each unique location so for example, if in column A Stock Location PB12345 is repeated 6 times and has a quantiity of 500 - when the SUMIF runs through the the two columns, it only includes the sum of 500 from PB12345 once and not include the six times its repeated

Ive attached a sample file and the desired result in cell F1 - the formula im using shows how many times its repeated but not the sum ?

If you could help I would be very grateful

J
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

=SUM((FREQUENCY(MATCH(A2:A10,A2:A10,0),ROW(A2:A10)-ROW(A2)+1)>0)*B2:B11)

Open in new window

Change the range reference as per your requirement and remember to exceed the range reference by one cell from column B as you can see that the referred range from column A is A2:A10 but range referred from column B is B2:B11.

UniqueSum.jpg
Jase AlexanderCompliance Manager

Author

Commented:
HI Subodh

Thanks so much for this - works perfect !!

J
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Jase! Glad it worked as desired.
Thanks for the feedback.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial