VLOOKUP but add values of the same order number?

[Ben171]

I have created a very simple workbook to demonstrate what I am asking.

Lets say I have one sheet called 'order numbers' and a second sheet called 'Carriage charge'

On sheet 2 "Carriage charge" you can see in column D i have a blank column in which i want to add the order value from sheet1

However as you can see from sheet 1 "order numbers" there is one order with the same order number 3 times. Therefore when I do my lookup, i would like it to add these 3 orders together so that it displays the total of £110, rather than find the first one and display £30.

hope that makes sense

 

[Joe4]

You would not use VLOOKUP for that (VLOOKUP will only return the first value).
You would use the SUMIF function, to sum all values matching the ID you want.
See: MS Excel: How to use the SUMIF Function (WS)

There is also a SUMIFS function, if you have more than one criterion:

MS Excel: How to use the SUMIFS Function (WS)

This Excel tutorial explains how to use the Excel SUMIFS function with syntax and examples. The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.

www.techonthenet.com

 

[Ben171]

You would not use VLOOKUP for that (VLOOKUP will only return the first value).
You would use the SUMIF function, to sum all values matching the ID you want.
See: MS Excel: How to use the SUMIF Function (WS)

There is also a SUMIFS function, if you have more than one criterion:

MS Excel: How to use the SUMIFS Function (WS)

This Excel tutorial explains how to use the Excel SUMIFS function with syntax and examples. The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.

www.techonthenet.com

whoops, thank you!

I will check this out as I have not used this function before

 

[Joe4]

whoops, thank you!

I will check this out as I have not used this function before

No worries!

Those functions are pretty simple to use (they are easier to understand than VLOOKUP, IMO), and those links show examples.
If you run into any issues incorporating it into your worksheet, feel free to post back here to this thread for more assistance.

 

[Ben171]

No worries!

Those functions are pretty simple to use (they are easier to understand than VLOOKUP, IMO), and those links show examples.
If you run into any issues incorporating it into your worksheet, feel free to post back here to this thread for more assistance.

Using these examples i managed to figure it out and get what i wanted, thanks for your help

 

[Joe4]

You are welcome.
Glad I was able to help!